#coding=utf-8
from uliweb import expose
from sqlalchemy import *
from uliweb.orm import *
from sqlalchemy.sql import select,func
from sqlalchemy.sql import and_, or_, not_, outerjoin
import datetime
from datetime import date
from uliweb import decorators

def __begin__():
    from uliweb import function
    return function('require_login')()

@expose('/charts')
def charts():
    return {}

def get_date():
    from uliweb import request
    from uliweb import settings
    
    begin_Date = request.GET.get('begin_date','')
    end_Date = request.GET.get('end_date','')
    if begin_Date =='':
        begin_Date = settings.PARA.begin_date
    else:
         begin_Date = begin_Date[:10]
    if end_Date =='':
        end_Date = str(date.today())
    else:
        end_Date = end_Date[:10]    
    begin_Date = datetime.datetime.strptime(begin_Date, '%Y-%m-%d')
    end_Date = datetime.datetime.strptime(end_Date, '%Y-%m-%d')
    begin_Date = begin_Date.date()
    end_Date = end_Date.date()
    return begin_Date,end_Date

def get_count_view(groupby):
    from uliweb.orm import get_model
    from uliweb.core.html import Tag
    
    begin_Date,end_Date = get_date()
    #初始化data,
    data = {}
    tx = get_model('tx')
    kfzx = get_model('kfzx')
    sys = get_model('system')
    #stages = ['11','12','13','14','15','16','17','18','19','1A','1B','21','22','23','24','25','26','31','32','33','34']
    stages = ['zy_wks','zy_ky','zy_rwsq','zy_sstg','zy_yc','zy_zt','zy_wc','zy_qx',
              'gz_wks','gz_ky','gz_rwsq','gz_sstg','gz_yc','gz_zt','gz_wc','gz_qx',
              'yb_wks','yb_ky','yb_rwsq','yb_sstg','yb_yc','yb_zt','yb_wc','yb_qx',
              'total']
    if groupby == 'tx':
        for obj in tx.all():
            data[obj.id] ={}
            for y in stages:
                data[obj.id][y] = 0
    
    if groupby == 'zx':
        for obj in kfzx.all():
            data[obj.id] ={}
            for y in stages:
                data[obj.id][y] = 0
            
    if groupby == 'sys':
        for obj in sys.all():
            data[obj.id] ={}
            for y in stages:
                data[obj.id][y] = 0
    
    data[u'总计']={}
    for y in stages:
        data[u'总计'][y] = 0
            
    req = get_model('requirement')
    if groupby == 'tx':
        zy = select([req.c.tx,req.c.stage,func.count(req.c.id)],or_(and_(req.c.modified_date>=begin_Date,req.c.stage.in_(['19','1A','CL','FN']),req.c.care_level=='3'),and_(~req.c.stage.in_(['19','1A','CL','FN']),req.c.care_level=='3'))).group_by(req.c.tx,req.c.stage)
        gz = select([req.c.tx,req.c.stage,func.count(req.c.id)],or_(and_(req.c.modified_date>=begin_Date,req.c.stage.in_(['19','1A','CL','FN']),req.c.care_level=='2'),and_(~req.c.stage.in_(['19','1A','CL','FN']),req.c.care_level=='2'))).group_by(req.c.tx,req.c.stage)
        yb = select([req.c.tx,req.c.stage,func.count(req.c.id)],or_(and_(req.c.modified_date>=begin_Date,req.c.stage.in_(['19','1A','CL','FN']),req.c.care_level=='1'),and_(~req.c.stage.in_(['19','1A','CL','FN']),req.c.care_level=='1'))).group_by(req.c.tx,req.c.stage)
#        gz = select([req.c.tx,req.c.stage,func.count(req.c.id)],and_(req.c.modified_date>=begin_Date,req.c.care_level=='2')).group_by(req.c.tx,req.c.stage)
#        yb = select([req.c.tx,req.c.stage,func.count(req.c.id)],and_(req.c.modified_date>=begin_Date,req.c.care_level=='1')).group_by(req.c.tx,req.c.stage)
        #old = select([req.c.tx,req.c.stage,func.count(req.c.id)],and_(req.c.created_date<begin_Date,req.c.begin_date>=begin_Date)).group_by(req.c.tx,req.c.stage)
#        old = select([req.c.tx,req.c.stage,func.count(req.c.id)],or_(and_(req.c.created_date<begin_Date,req.c.modified_date<begin_Date,~req.c.stage.in_(['19','1A','FN'])),and_(req.c.created_date<begin_Date,req.c.modified_date<=end_Date,req.c.modified_date>=begin_Date))).group_by(req.c.tx,req.c.stage)
    if groupby == 'zx':
        new = select([sys.c.kfzx,req.c.stage,func.count(req.c.id)],
            and_(req.c.created_date>=begin_Date,
            req.c.created_date<=end_Date,
            req.c.main_sys==sys.c.id)).group_by(sys.c.kfzx,req.c.stage)
        #old = select([sys.c.kfzx,req.c.stage,func.count(req.c.id)],and_(req.c.created_date<begin_Date,req.c.begin_date>=begin_Date,req.c.main_sys==sys.c.id)).group_by(sys.c.kfzx,req.c.stage)
        old = select([sys.c.kfzx,req.c.stage,func.count(req.c.id)],
            or_(and_(req.c.created_date<begin_Date,
            req.c.modified_date<begin_Date,
            ~req.c.stage.in_(['19','1A','FN']),
            req.c.main_sys==sys.c.id),
            and_(req.c.created_date<begin_Date,
            req.c.modified_date<=end_Date,
            req.c.modified_date>=begin_Date,
            req.c.main_sys==sys.c.id))).group_by(sys.c.kfzx,req.c.stage)
    if (request.user.department=='2')and(groupby == 'sys'):
        i = request.user._kfzx_
        new = select([req.c.main_sys,req.c.stage,func.count(req.c.id)],
            and_(req.c.created_date>=begin_Date,
            req.c.created_date<=end_Date,
            req.c.main_sys==sys.c.id,
            sys.c.kfzx==i))\
            .group_by(req.c.main_sys,req.c.stage)
        old = select([req.c.main_sys,req.c.stage,func.count(req.c.id)],
            or_(and_(req.c.created_date<begin_Date,
            req.c.modified_date<begin_Date,
            ~req.c.stage.in_(['19','1A','FN']),
            req.c.main_sys==sys.c.id,
            sys.c.kfzx==i),
            and_(req.c.created_date<begin_Date,
            req.c.modified_date<=end_Date,
            req.c.modified_date>=begin_Date,
            req.c.main_sys==sys.c.id,
            sys.c.kfzx==i)))\
            .group_by(req.c.main_sys,req.c.stage)        
    elif groupby == 'sys':
        new = select([req.c.main_sys,req.c.stage,func.count(req.c.id)],and_(req.c.created_date>=begin_Date,req.c.created_date<=end_Date)).group_by(req.c.main_sys,req.c.stage)
        old = select([req.c.main_sys,req.c.stage,func.count(req.c.id)],or_(and_(req.c.created_date<begin_Date,req.c.modified_date<begin_Date,~req.c.stage.in_(['19','1A','FN'])),and_(req.c.created_date<begin_Date,req.c.modified_date<=end_Date,req.c.modified_date>=begin_Date))).group_by(req.c.main_sys,req.c.stage)
#    print '+++++++++++',zy
    zy_rs = zy.execute()
    gz_rs = gz.execute()
    yb_rs = yb.execute()
         
    for row in zy_rs:
        a, stage, count = row
#        if not stage:
#            stage = '00'
#        if stage == 'CL':
#            continue
        stage = settings.PARA.zy_map[stage]        
        data[a][stage] += count
        data[a]['total'] += count
        data[u'总计'][stage] += count
        data[u'总计']['total'] += count

    for row in gz_rs:
        a, stage, count = row
#        if not stage:
#           stage = '00'
#        if stage == 'CL':
#            continue
        stage = settings.PARA.gz_map[stage]
        data[a][stage] += count
        data[a]['total'] += count
        data[u'总计'][stage] += count
        data[u'总计']['total'] += count
    for row in yb_rs:
        a, stage, count = row
#        if not stage:
#           stage = '00'
#        if stage == 'CL':
#            continue
        stage = settings.PARA.yb_map[stage]
        data[a][stage] += count
        data[a]['total'] += count
        data[u'总计'][stage] += count
        data[u'总计']['total'] += count
    
    x = []
    if groupby == 'tx':
        for t, v in data.items():                    
            if t==u'总计':
                v['tx_name']=t
            else:
                v['tx_name'] = tx.get(t).name
            x.append(v)

    if groupby == 'zx':
        for t, v in data.items():        
            if t==u'总计':
                v['zx_name']=t
            else:
                v['zx_name'] = kfzx.get(t).name
            x.append(v)
    
    if groupby == 'sys':
        for t, v in data.items():        
            if t==u'总计':
                v['sys_name']=t
            else:
                v['sys_name'] = sys.get(t).name
            if v['total']>0:
                x.append(v)
    
#    d = dict(settings.PARA.TX)
    return x


#需求数量按条线分布
@decorators.require_login
@expose('/charts/requirements/count')
def charts_requirements_count():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

#    begin_Date,end_Date = get_date()
    from query import create_requirement_change_week_query
    

    #获取一周起止日期
    
    query = create_requirement_change_week_query(url_for(charts_requirement_change_week_query))
    c = query.run()
    
    if c.get('begin_date'):
        begin_Date = c['begin_date']
    else:
        begin_Date = date.today()
    begin_Date = begin_Date - datetime.timedelta(days=(begin_Date.weekday()+2)%7)
    end_Date = begin_Date + datetime.timedelta(days=6)    

    
    data = {}
    x = get_count_view('tx')
    view =  SimpleListView(settings.PARA.requirments_tx_count, x,
       id='req_count_table',pagination=False)
    if 'download' in request.GET: 
               
#        fields_convert_map = {'weekreport':weekreport_title}
        return view.download('charts_requirement_change_week_query.xls', action='download', timeout=0)
    elif 'data' in request.values:
        return json(view.json())
    else:
        result = view.run(head=True, body=True)
        result['table'] = view
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        
#        result.update({'query_form':query.form})
        return result
    
#    if 'download' in request.GET:
#        return view.download('charts_requirements_count_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                    
#    else:
#        result = view.run()
#        result['begin_date'] = begin_Date
#        result['end_date'] = end_Date
#        return result

#需求数量按中心分布
@decorators.require_login
@expose('/charts/requirements/countzx')
def charts_requirements_countzx():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    
    data = {}
    x = get_count_view('zx')
    view =  SimpleListView(settings.PARA.requirments_zx_count, x,
        id='req_count_table',pagination=False)
                
    if 'download' in request.GET:
        return view.download('charts_requirements_countzx_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result
    

#需求数量按系统分布
@decorators.require_login
@expose('/charts/requirements/countsys')
def charts_requirements_countsys():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
        
    data = {}
    x = get_count_view('sys')
    view =  SimpleListView(settings.PARA.requirments_sys_count, x,
        id='req_count_table',pagination=False)
                
    if 'download' in request.GET:
        return view.download('charts_requirements_countsys_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                    
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

    
#任务书按中心分布
@expose('/charts/assignment/date/kfzx')
def charts_assignment_date_kfzx():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    pageno = int(request.GET.get('pageno', 0))    
    begin_Date,end_Date = get_date()
    
    #初始化data,
    data = {}
    stages = ['total','new_xm','new_zxm','new_fxm','new_zxcg','old_xm','old_zxm','old_fxm','old_zxcg']
    kfzx = get_model('kfzx')
    for obj in kfzx.all():
        data[obj.id] ={}
        for y in stages:
            data[obj.id][y] = 0
    data[u'总计']={}
    for y in stages:
        data[u'总计'][y] = 0
    
    ass = get_model('assignment')
    new = select([ass.c.kfzx,ass.c.type,
            func.count(func.distinct(func.substr(ass.c.ass_id,1,func.instr(ass.c.ass_id,'_'))))],
            and_(ass.c.release_date>=begin_Date,
            ass.c.release_date<=end_Date,
            )).group_by(ass.c.kfzx,ass.c.type)
    old = select([ass.c.kfzx,ass.c.type,
            func.count(func.distinct(func.substr(ass.c.ass_id,1,func.instr(ass.c.ass_id,'_'))))],
            or_(and_(ass.c.release_date<begin_Date,
                    ass.c.stage!='FN'),
                and_(ass.c.release_date<begin_Date,
                    ass.c.stage=='FN',
                    ass.c.modified_date>=begin_Date)
            )).group_by(ass.c.kfzx,ass.c.type)    
    new_rs = new.execute()
    old_rs = old.execute()   
         
    for row in new_rs:
        zx, type, count = row
        type = settings.PARA.assignment_new_map[type]        
        data[int(zx)][type] += count
        data[int(zx)]['total'] += count
        data[u'总计'][type] += count
        data[u'总计']['total'] += count
    
    for row in old_rs:
        zx, type, count = row
        type = settings.PARA.assignment_old_map[type]
        data[int(zx)][type] += count
        data[int(zx)]['total'] += count
        data[u'总计'][type] += count
        data[u'总计']['total'] += count

    x = []
    for t, v in data.items():        
        if t==u'总计':
            v['zx_name']=t
        else:
            v['zx_name'] = kfzx.get(t).name
        x.append(v)
    
    view =  SimpleListView(settings.PARA.assignment_zx_count, 
        x,id='req_count_table',pagination=False)
                
    if 'download' in request.GET:
        return view.download('charts_assignment_date_kfzx'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result
    
    
#任务书按系统分布
@expose('/charts/assignment/date/sys')
def charts_assignment_date_sys():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    pageno = int(request.GET.get('pageno', 0))    
    begin_Date,end_Date = get_date()
    
    #初始化data,
    sys = get_model('system')
    data = {}
    stages = ['total','new_xm','new_zxm','new_fxm','new_zxcg','old_xm','old_zxm','old_fxm','old_zxcg']
    for obj in sys.all():
        data[obj.id] ={}
        for y in stages:
            data[obj.id][y] = 0
    data[u'总计']={}
    for y in stages:
        data[u'总计'][y] = 0
    
    ass = get_model('assignment')
    ass_sys = ass.systems.table
    user_zx = request.user._kfzx_
    if request.user.department=='2':        
        new = select([ass_sys.c.system_id,ass.c.type,
            func.count(func.distinct(func.substr(ass.c.ass_id,1,func.instr(ass.c.ass_id,'_'))))],
            and_(ass.c.release_date>=begin_Date,
            ass.c.release_date<=end_Date,
            ass.c.id==ass_sys.c.assignment_id,
            ass.c.kfzx==user_zx,
            )).group_by(ass_sys.c.system_id,ass.c.type)
        old = select([ass_sys.c.system_id,ass.c.type,
            func.count(func.distinct(func.substr(ass.c.ass_id,1,func.instr(ass.c.ass_id,'_'))))],
            and_(
                ass.c.id==ass_sys.c.assignment_id,
                ass.c.kfzx==user_zx,
                or_(and_(ass.c.release_date<begin_Date,
                    ass.c.stage!='FN'),
                    and_(ass.c.release_date<begin_Date,
                    ass.c.stage=='FN',
                    ass.c.modified_date>=begin_Date)
                    )  
                )).group_by(ass_sys.c.system_id,ass.c.type)    
    else:        
        new = select([ass_sys.c.system_id,ass.c.type,
            func.count(func.distinct(func.substr(ass.c.ass_id,1,func.instr(ass.c.ass_id,'_'))))],
            and_(ass.c.release_date>=begin_Date,
            ass.c.release_date<=end_Date,
            ass.c.id==ass_sys.c.assignment_id,
            )).group_by(ass_sys.c.system_id,ass.c.type)
        old = select([ass_sys.c.system_id,ass.c.type,
            func.count(func.distinct(func.substr(ass.c.ass_id,1,func.instr(ass.c.ass_id,'_'))))],
            and_(
                ass.c.id==ass_sys.c.assignment_id,
                or_(and_(ass.c.release_date<begin_Date,
                    ass.c.stage!='FN'),
                    and_(ass.c.release_date<begin_Date,
                    ass.c.stage=='FN',
                    ass.c.modified_date>=begin_Date)
                    )  
                )).group_by(ass_sys.c.system_id,ass.c.type)    
    new_rs = new.execute()
    old_rs = old.execute()   
    
         
    for row in new_rs:
        system, type, count = row
        type = settings.PARA.assignment_new_map[type] 
        data[int(system)][type] += count
        data[int(system)]['total'] += count
        data[u'总计'][type] += count
        data[u'总计']['total'] += count
    
    for row in old_rs:
        system, type, count = row
        type = settings.PARA.assignment_old_map[type]
        data[int(system)][type] += count
        data[int(system)]['total'] += count
        data[u'总计'][type] += count
        data[u'总计']['total'] += count

    x = []
    for t, v in data.items():        
        if t==u'总计':
            v['zx_name']=t
        else:
            v['zx_name'] = sys.get(t).name
        if v['total']>0:
            x.append(v)
    
    view =  SimpleListView(settings.PARA.assignment_zx_count, 
        x,id='req_count_table',pagination=False)
                
    if 'download' in request.GET:
        return view.download('charts_assignment_date_kfzx'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result


#投产版本分布月报表
@expose('/charts/version/month')
def charts_version_month():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    
    pageno = int(request.GET.get('pageno', 0))    
    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    kfzx = get_model('kfzx')
    #生成表头
    header = []
    header.append({'name':'month','verbose_name':'月份', 'width':70})
    header.append({'name':'total','verbose_name':'合计', 'width':70})
    for obj in kfzx.all():
        i={}
        i['name']='zx_'+str(obj.id)
        i['verbose_name']=obj.name
        i['width']=70
        header.append(i)
    #初始化结构
    x = []
    a = get_month_list(begin_Date,end_Date)
    for i in a:
        j = {}
        j['month']=i
        j['total']=0
        for k in kfzx.all():
            j['zx_'+str(k.id)]=0

        x.append(j)
    total={}
    total['month']=u'总计'
    total['total']=0
    for k in kfzx.all():
        total['zx_'+str(k.id)]=0
        
    x.append(total)
    #生成数据
    task = get_model('tasks_develop')
    sys = get_model('system')
    new = select([func.subStr(task.c.version_date,1,7),
        sys.c.kfzx,func.count(task.c.id)],
        and_(task.c.version_date>=begin_Date,
        task.c.version_date<=end_Date,
        task.c.task_sys==sys.c.id))\
        .group_by(func.subStr(task.c.version_date,1,7),sys.c.kfzx)
    new_rs = new.execute()
    
    for row in new_rs:
        month, zx, count = row
        if month[5]=='0':
            month = month[0:5]+month[6]
        for i in x:
            if i['month']==month:
                i['zx_'+str(zx)]+=count
                i['total']+=count
            if i['month']==u'总计':
                i['zx_'+str(zx)]+=count                
                i['total']+=count
                    
    #展示        
    view =  SimpleListView(header, x, id='req_count_table',pagination=False)
    
    if 'download' in request.GET:
        return view.download('charts_version_month'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result


#投产版本分布季报表
@expose('/charts/version/season')
def charts_version_season():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    
    season_map = {1:'1',2:'1',3:'1',4:'2',5:'2',6:'2',
        7:'3',8:'3',9:'3',10:'4',11:'4',12:'4'}
    pageno = int(request.GET.get('pageno', 0))    
    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    kfzx = get_model('kfzx')
    #生成表头
    header = []
    header.append({'name':'month','verbose_name':'月份', 'width':70})
    header.append({'name':'total','verbose_name':'合计', 'width':70})
    for obj in kfzx.all():
        i={}
        i['name']='zx_'+str(obj.id)
        i['verbose_name']=obj.name
        i['width']=70
        header.append(i)
    #初始化结构
    x = []
    a = get_season_list(begin_Date,end_Date)
    for i in a:
        j = {}
        j['month']=i
        j['total']=0
        for k in kfzx.all():
            j['zx_'+str(k.id)]=0
    
        x.append(j)
    total={}
    total['month']=u'总计'
    total['total']=0
    for k in kfzx.all():
        total['zx_'+str(k.id)]=0
        
    x.append(total)
    #生成数据
    task = get_model('tasks_develop')
    sys = get_model('system')
    new = select([func.subStr(task.c.version_date,1,7),
        sys.c.kfzx,func.count(task.c.id)],
        and_(task.c.version_date>=begin_Date,
        task.c.version_date<=end_Date,
        task.c.task_sys==sys.c.id))\
        .group_by(func.subStr(task.c.version_date,1,7),sys.c.kfzx)
    new_rs = new.execute()
    
    for row in new_rs:
        month, zx, count = row
        a = month[5:7]
        if month[5]=='0':
            a = month[6]
            month = month[0:5]+month[6]
        for i in x:
            if i['month']==month[0:5]+season_map[int(a)]:
                i['zx_'+str(zx)]+=count
                i['total']+=count
            if i['month']==u'总计':
                i['zx_'+str(zx)]+=count                
                i['total']+=count
                    
    #展示        
    view =  SimpleListView(header, x, id='req_count_table',pagination=False)
    
    if 'download' in request.GET:
        return view.download('charts_version_season'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result
    

#延迟项目列表展示
@expose('/charts/plan/project/delay/list')
def charts_plan_project_delay_list():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from uliweb.utils.common import get_var
   
    ass = get_model('assignment')
    new = select([ass.c.name,ass.c.stage,ass.c.begin_date,ass.c.end_date,ass.c.status,ass.c.delay_classes,ass.c.delay_reason],and_(ass.c.status.in_(['2','4','5']),ass.c.type=='1'))
    new_rs = new.execute()
    x=[]     
    for row in new_rs:
        name, stage, begin_date,end_date,status,delay_classes,delay_reason = row
        #type = settings.PARA.assignment_new_map[type]  
        row = list(row)
        row[1]=dict(settings.PARA.ASSIGNMENT_STAGE['1'])[stage]
        row[4]=dict(settings.PARA.ASSIGNMENT_STATUS)[status]
        if delay_classes:
            row[5]=dict(settings.PARA.ASSIGNMENT_DELAY_CLASSES)[delay_classes]
        x.append(row)
    view =  SimpleListView(settings.PARA.assignment_plan_delay, x,
        pagination=False,id='charts_plan_project_delay_list')

                
#    if 'data' in request.GET:
#        result = view.run(head=False, body=True)
#        return json(result)
#    else:
#        result = view.run(head=True, body=False)
#        return result

    if 'download' in request.GET:
          download_name = 'charts_plan_project_delay_list' + '.xls'
          return view.download(download_name, action='download', timeout=10)               
    else:
          result = view.run()
    #      result['begin_date'] = begin_Date
    #      result['end_date'] = end_Date
          return result
    
#延迟子项目列表展示
@expose('/charts/plan/subproject/delay/list')
def charts_plan_subproject_delay_list():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from uliweb.utils.common import get_var
    
    ass = get_model('assignment')
    new = select([ass.c.name,ass.c.stage,ass.c.begin_date,ass.c.end_date,ass.c.status,ass.c.delay_classes,ass.c.delay_reason],and_(ass.c.status.in_(['2','4','5']),ass.c.type=='2'))
    new_rs = new.execute()
    x=[]
    for row in new_rs:
        name, stage, begin_date,end_date,status,delay_classes,delay_reason = row
        #type = settings.PARA.assignment_new_map[type]  
        row = list(row)
        row[1]=dict(settings.PARA.ASSIGNMENT_STAGE['2'])[stage]
        row[4]=dict(settings.PARA.ASSIGNMENT_STATUS)[status]
        row[5]=dict(settings.PARA.ASSIGNMENT_DELAY_CLASSES)[delay_classes]
        x.append(row)
            
    view =  SimpleListView(settings.PARA.assignment_plan_delay, x,
        pagination=False,id='charts_plan_subproject_delay_list')
                
#    if 'data' in request.GET:
#        result = view.run(head=False, body=True)
#        return json(result)
#    else:
#        result = view.run(head=True, body=False)
#        return result

    if 'download' in request.GET:
          download_name = 'charts_plan_subproject_delay_list' + '.xls'
          return view.download(download_name, action='download', timeout=10)               
    else:
          result = view.run()
    #      result['begin_date'] = begin_Date
    #      result['end_date'] = end_Date
          return result
    
#延迟非项目任务列表展示
@expose('/charts/plan/nproject/delay/list')
def charts_plan_nproject_delay_list():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from uliweb.utils.common import get_var
    
    ass = get_model('assignment')
    new = select([ass.c.name,ass.c.stage,ass.c.begin_date,ass.c.end_date,ass.c.status,ass.c.delay_classes,ass.c.delay_reason],and_(ass.c.status.in_(['2','4','5']),ass.c.type=='3'))
    new_rs = new.execute()
    x=[]     
    for row in new_rs:
        name, stage, begin_date,end_date,status,delay_classes,delay_reason = row
        #type = settings.PARA.assignment_new_map[type]  
        row = list(row)
        row[1]=dict(settings.PARA.ASSIGNMENT_STAGE['3'])[stage]
        row[4]=dict(settings.PARA.ASSIGNMENT_STATUS)[status]
        row[5]=dict(settings.PARA.ASSIGNMENT_DELAY_CLASSES)[delay_classes]
        x.append(row)
            
    view =  SimpleListView(settings.PARA.assignment_plan_delay, x,
        pagination=False,id='charts_plan_nproject_delay_list')
        
#    if 'data' in request.GET:
#        result = view.run(head=False, body=True)
#        return json(result)
#    else:
#        result = view.run(head=True, body=False)
#        return result

    if 'download' in request.GET:
          download_name = 'charts_plan_nproject_delay_list' + '.xls'
          return view.download(download_name, action='download', timeout=10)               
    else:
          result = view.run()
    #      result['begin_date'] = begin_Date
    #      result['end_date'] = end_Date
          return result

#取日期的年月值，返回整数    
def get_yearmonth(in_date):
    return in_date.year*100+in_date.month

#按照时间区间查询各开发中心工作量
@expose('/charts/resource/zx/date')
def charts_resource_zx_date():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    
    task = get_model('tasks_details')
    zx = get_model('kfzx')
    sql = select([zx.c.name,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),
                func.sum(task.c.amount_inner_design),
                func.sum(task.c.amount_outer_design),
                func.sum((task.c.amount_inner_out8+task.c.amount_inner_weekend)),
                func.sum((task.c.amount_outer_out8+task.c.amount_outer_weekend))],
                and_(task.c.year_month>=begin,
                task.c.year_month<=end,
                zx.c.id==task.c.kfzx)).group_by(zx.c.name)
    sql_rs = sql.execute()
    
    #生成数据
    j = [0]*len(settings.PARA.resource_zx_date)
    j.insert(0,u'总计')
    x = []
    for row in sql_rs:
        name,zx_sum,gs_sum,zx_sum_design,gs_sum_design,zx_sum_extra,gs_sum_extra = row
        row = list(row)
        row.insert(1,(zx_sum+gs_sum))
        x.append(row)
        for i in range(1,len(row)):
            j[i]+=row[i]
    x.append(j)
            
    view =  SimpleListView(settings.PARA.resource_zx_date,x,pagination=False)
                
    if 'download' in request.GET:
        return view.download('charts_resource_zx_date_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result


#按照时间区间查询各开发中心工作量
@expose('/charts/manage/resource/type')
@expose('/charts/resource/type/date')
def charts_resource_type_date():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from query import create_manage_resource_type_date_query
    from uliweb.core.html import Tag
    from decimal import Decimal
    import time
    import datetime
    from datetime import date
    
    query = create_manage_resource_type_date_query(url_for(charts_resource_type_date))
    c = query.run()
    
#    sel_yearmonth = None
    be_year_list = dict(settings.PARA.YEAR_LIST)
    be_month_list = dict(settings.PARA.MONTH_LIST)
    
    if (c.get('begin_year') and c.get('begin_month')):
        begin_Date = datetime.date(*time.strptime(str(be_year_list[int(c['begin_year'])])+'-'+str(be_month_list[int(c['begin_month'])])+'-'+'01', "%Y-%m-%d")[:3])
        begin = int(be_year_list[int(c['begin_year'])])*100+int(be_month_list[int(c['begin_month'])])
    if (c.get('end_year') and c.get('end_month')):
        end_Date = datetime.date(*time.strptime(str(be_year_list[int(c['end_year'])])+'-'+str(be_month_list[int(c['end_month'])])+'-'+'01', "%Y-%m-%d")[:3])
        end = int(be_year_list[int(c['end_year'])])*100+int(be_month_list[int(c['end_month'])])
    
#    begin_Date,end_Date = get_date()
#    begin_month = begin_Date.month
#    end_month = end_Date.month
#    begin = get_yearmonth(begin_Date)
#    end = get_yearmonth(end_Date)            
    month_list = get_month_list_new(begin_Date,end_Date)
#    print '++++++++',begin,end,month_list
    kfzx = get_model('kfzx')
    #生成表头
    header = []
    header.append({'name':'type','verbose_name':u'类型', 'width':150})
    header.append({'name':'total','verbose_name':u'合计', 'width':70})
    header.append({'name':'total_zx','verbose_name':u'合计/中心', 'width':70})
    header.append({'name':'total_gs','verbose_name':u'合计/公司', 'width':70})
    
    for obj in kfzx.all():
        i={}
        i['name']=str(obj.id)+'_zx'
        i['verbose_name']=obj.name+'/'+u'中心'
        i['width']=70
        header.append(i)
        j={}
        j['name']=str(obj.id)+'_gs'
        j['verbose_name']=obj.name+'/'+u'公司'
        j['width']=70
        header.append(j)
    #初始化结构
    x = []
    for i in settings.PARA.TASK_TYPE:
        j = {}
        j['type']=i[0]
        j['total']=0
        j['total_zx']=0
        j['total_gs']=0
        for k in kfzx.all():
            j[str(k.id)+'_zx']=0
            j[str(k.id)+'_gs']=0
        x.append(j)
    total={}
    total['type']=u'总计'
    total['total']=0
    total['total_zx']=0
    total['total_gs']=0
    for k in kfzx.all():
        total[str(k.id)+'_zx']=0
        total[str(k.id)+'_gs']=0
    x.append(total)
    
    task = get_model('tasks_details')
    sql = select([task.c.task_type,task.c.kfzx,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input)],
                and_(task.c.year_month>=begin,
                task.c.year_month<=end)).group_by(task.c.task_type,task.c.kfzx)
    sql_rs = sql.execute()
    
    #生成数据
    for row in sql_rs:
        name,zx,zx_sum,gs_sum = row
        if not zx:
            continue
        for i in x:
            if i['type']==name:
                i[str(zx)+'_zx']+=zx_sum
                i[str(zx)+'_gs']+=gs_sum
                i['total_zx']+=zx_sum
                i['total_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
            if i['type']==u'总计':
                i[str(zx)+'_zx']+=zx_sum                
                i[str(zx)+'_gs']+=gs_sum                
                i['total_zx']+=zx_sum
                i['total_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
    
    days = get_monthlist_days(month_list)
    for i in x:
        i['total']=str(round(i['total']/days,1))
        i['total_zx']=str(round(i['total_zx']/days,1))
        i['total_gs']=str(round(i['total_gs']/days,1))
        for j in kfzx.all():
            i[str(j.id)+'_zx']=str(round(i[str(j.id)+'_zx']/days,1))
            i[str(j.id)+'_gs']=str(round(i[str(j.id)+'_gs']/days,1))
        
        if i['type']!=u'总计':
            i['type']= unicode(dict(settings.PARA.TASK_TYPE)[i['type']],'utf8')

    pageno = int(request.values.get('page', 1)) - 1
    rows_per_page = int(request.values.get('rows', settings.get_var('PARA/ROWS_PER_PAGE', 10)))

    #展示        
    view =  SimpleListView(header, x, id='req_count_table',table_width=True,
        pagination=True, pageno=pageno,rows_per_page=rows_per_page)

    if 'download' in request.GET:
        fields_convert_map = {}
        return view.download('charts_resource_type_date_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', 
            timeout=0)
    elif 'data' in request.values:
        return json(view.json())
    else:
        result = view.run(head=True, body=True)
        result['table'] = view
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        result.update({'query_form':query.form})
        return result

def get_month_list(begin,end):
    x = []
    year=begin.year
    month=begin.month
    i = end.year-begin.year
    j=0
    if i==0:
        j=end.month-begin.month+1
    if i>0:
        j=end.month+(i-1)*12+(12-begin.month+1)
    for k in range(0,j):
        if month<=12:
            a=str(year)+'-'+str(month)
        if month==13:
            month=1
            year+=1
            a=str(year)+'-'+str(month)
        month += 1
        x.append(a)
    return x
def get_month_list_new(begin,end):
    x = []
    year=begin.year
    month=begin.month
    i = end.year-begin.year
    j=0
    if i==0:
        j=end.month-begin.month+1
    if i>0:
        j=end.month+(i-1)*12+(12-begin.month+1)
    for k in range(0,j):
        if month<=12:
            a=year*100+month
        if month==13:
            month=1
            year+=1
            a=year*100+month
        month += 1
        x.append(str(a))
    return x

def get_monthlist_days(month_list):
    days = 0
    for i in month_list:
        if i in dict(settings.PARA.MONTH_DAYS):
            days += int(dict(settings.PARA.MONTH_DAYS)[i])
        else:
            days += 22
    return days

def get_month_days(month):
    days = 0
    if month in dict(settings.PARA.MONTH_DAYS):
        days += int(dict(settings.PARA.MONTH_DAYS)[month])
    else:
        days += 22
    return days

#各开发中心工作量月报
@expose('/charts/resource/zx/month')
def charts_resource_zx_month():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    kfzx = get_model('kfzx')
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            

    #生成表头
    header = []
    header.append({'name':'month','verbose_name':'月份', 'width':70})
    header.append({'name':'total','verbose_name':'合计', 'width':70})
    header.append({'name':'total_zx','verbose_name':'合计/中心', 'width':70})
    header.append({'name':'total_gs','verbose_name':'合计/公司', 'width':70})
    
    for obj in kfzx.all():
        i={}
        i['name']=str(obj.id)+'_zx'
        i['verbose_name']=obj.name+'/'+u'中心'
        i['width']=70
        header.append(i)
        j={}
        j['name']=str(obj.id)+'_gs'
        j['verbose_name']=obj.name+'/'+u'公司'
        j['width']=70
        header.append(j)
    #初始化结构
    x = []
    a = get_month_list(begin_Date,end_Date)
    for i in a:
        j = {}
        j['month']=i
        j['total']=0
        j['total_zx']=0
        j['total_gs']=0
        for k in kfzx.all():
            j[str(k.id)+'_zx']=0
            j[str(k.id)+'_gs']=0
        x.append(j)
    total={}
    total['month']=u'总计'
    total['total']=0
    total['total_zx']=0
    total['total_gs']=0
    for k in kfzx.all():
        total[str(k.id)+'_zx']=0
        total[str(k.id)+'_gs']=0
    x.append(total)
    #生成数据
    task = get_model('tasks_details')
    sql = select([task.c.year,task.c.month,task.c.kfzx,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),],
                and_(task.c.year_month>=begin,
                task.c.year_month<=end))\
                .group_by(task.c.year,task.c.month,task.c.kfzx)
    sql_rs = sql.execute()
    
    for row in sql_rs:
        year,month,zx,zx_sum,gs_sum = row
        if not zx:
            continue
        for i in x:
            if i['month']==str(year)+'-'+str(month):
                i[str(zx)+'_zx']+=zx_sum
                i[str(zx)+'_gs']+=gs_sum
                i['total_zx']+=zx_sum
                i['total_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
            if i['month']==u'总计':
                i[str(zx)+'_zx']+=zx_sum                
                i[str(zx)+'_gs']+=gs_sum                
                i['total_zx']+=zx_sum
                i['total_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
#    #转为整数
#    for i in x:
#        i['total']=int(i['total'])
#        i['total_zx']=int(i['total_zx'])
#        i['total_gs']=int(i['total_gs'])
#        for k in kfzx.all():
#            i[str(k.id)+'_zx']=int(i[str(k.id)+'_zx'])
#            i[str(k.id)+'_gs']=int(i[str(k.id)+'_gs'])
#    
    #展示        
    view =  SimpleListView(header, x, id='req_count_table',pagination=False)

    if 'download' in request.GET:
        return view.download('charts_resource_zx_month_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result


#各类型工作工作量月报
@expose('/charts/resource/type/month')
def charts_resource_type_month():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    #生成表头
    header = []
    header.append({'name':'month','verbose_name':'月份', 'width':100})
    header.append({'name':'total','verbose_name':'合计', 'width':70})
    header.append({'name':'total_zx','verbose_name':'合计/中心', 'width':70})
    header.append({'name':'total_gs','verbose_name':'合计/公司', 'width':70})
    
    for obj in settings.PARA.TASK_TYPE:
        i={}
        i['name']=str(obj[0])+'_zx'
        i['verbose_name']=unicode(obj[1],'utf-8')+u'/'+u'中心'
        i['width']=70
        header.append(i)
        j={}
        j['name']=str(obj[0])+'_gs'
        j['verbose_name']=unicode(obj[1],'utf-8')+u'/'+u'公司'
        j['width']=70
        header.append(j)
    #初始化结构
    x = []
    a = get_month_list(begin_Date,end_Date)
    for i in a:
        j = {}
        j['month']=i
        j['total']=0
        j['total_zx']=0
        j['total_gs']=0
        for k in settings.PARA.TASK_TYPE:
            j[str(k[0])+'_zx']=0
            j[str(k[0])+'_gs']=0
        x.append(j)
    total={}
    total['month']=u'总计'
    total['total']=0
    total['total_zx']=0
    total['total_gs']=0
    for k in settings.PARA.TASK_TYPE:
        total[str(k[0])+'_zx']=0
        total[str(k[0])+'_gs']=0
    x.append(total)
    #生成数据
    task = get_model('tasks_details')
    sql = select([task.c.year,task.c.month,task.c.task_type,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),],
                and_(task.c.year_month>=begin,
                task.c.year_month<=end))\
                .group_by(task.c.year,task.c.month,task.c.task_type)
    sql_rs = sql.execute()
    
    for row in sql_rs:
        year,month,type,zx_sum,gs_sum = row
        if not type:
            continue
        for i in x:
            if i['month']==str(year)+'-'+str(month):
                i[str(type)+'_zx']+=zx_sum
                i[str(type)+'_gs']+=gs_sum
                i['total_zx']+=zx_sum
                i['total_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
            if i['month']==u'总计':
                i[str(type)+'_zx']+=zx_sum                
                i[str(type)+'_gs']+=gs_sum                
                i['total_zx']+=zx_sum
                i['total_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
                    
    #展示        
    view =  SimpleListView(header, x, id='req_count_table',pagination=False)

    if 'download' in request.GET:
        return view.download('charts_resource_type_month_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

def get_season_list(begin,end):
    #定义季度map
    season_map = {1:1,2:1,3:1,4:2,5:2,6:2,7:3,8:3,9:3,10:4,11:4,12:4}
    x = []
    year=begin.year
    month=begin.month
    i = end.year-begin.year
    j=0
    if i==0:
        j=end.month-begin.month+1
    if i>0:
        j=end.month+(i-1)*12+(12-begin.month+1)
    for k in range(0,j):
        if month<=12:
            a=str(year)+'-'+str(season_map[month])
        if month==13:
            month=1
            year+=1
            a=str(year)+'-'+str(season_map[month])
        month += 1
        if a not in x:
            x.append(a)        
    return x

#各开发中心工作量季报
@expose('/charts/resource/zx/season')
def charts_resource_zx_season():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    
    season_map = {1:1,2:1,3:1,4:2,5:2,6:2,7:3,8:3,9:3,10:4,11:4,12:4}
    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    kfzx = get_model('kfzx')
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            

    #生成表头
    header = []
    header.append({'name':'season','verbose_name':'季度', 'width':70})
    header.append({'name':'total','verbose_name':'合计', 'width':70})
    for obj in kfzx.all():
        i={}
        i['name']=str(obj.id)+'_zx'
        i['verbose_name']=obj.name+'/'+u'中心'
        i['width']=70
        header.append(i)
        j={}
        j['name']=str(obj.id)+'_gs'
        j['verbose_name']=obj.name+'/'+u'公司'
        j['width']=70
        header.append(j)
    #初始化结构
    x = []
    a = get_season_list(begin_Date,end_Date)
    for i in a:
        j = {}
        j['season']=i
        j['total']=0
        for k in kfzx.all():
            j[str(k.id)+'_zx']=0
            j[str(k.id)+'_gs']=0
        x.append(j)
    total={}
    total['season']=u'总计'
    total['total']=0
    for k in kfzx.all():
        total[str(k.id)+'_zx']=0
        total[str(k.id)+'_gs']=0
    x.append(total)
    #生成数据
    task = get_model('tasks_details')
    sql = select([task.c.year,task.c.month,task.c.kfzx,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),
                ],and_(task.c.year_month>=begin,
                task.c.year_month<=end,))\
                .group_by(task.c.year,task.c.month,task.c.kfzx)
    sql_rs = sql.execute()
    
    for row in sql_rs:
        year,month,zx,zx_sum,gs_sum = row
        if not zx:
            continue
        
        for i in x:
            if i['season']==str(year)+'-'+str(season_map[month]):
                i[str(zx)+'_zx']+=zx_sum
                i[str(zx)+'_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
            if i['season']==u'总计':
                i[str(zx)+'_zx']+=zx_sum                
                i[str(zx)+'_gs']+=gs_sum                
                i['total']+=zx_sum
                i['total']+=gs_sum
                    
    #展示        
    view =  SimpleListView(header, x, id='req_count_table',pagination=False)
    
    if 'download' in request.GET:
        return view.download('charts_resource_zx_season_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#各类型工作量季报
@expose('/charts/resource/type/season')
def charts_resource_type_season():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    
    season_map = {1:1,2:1,3:1,4:2,5:2,6:2,7:3,8:3,9:3,10:4,11:4,12:4}
    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            

    #生成表头
    header = []
    header.append({'name':'season','verbose_name':'季度', 'width':70})
    header.append({'name':'total','verbose_name':'合计', 'width':70})
    for obj in settings.PARA.TASK_TYPE:
        i={}
        i['name']=str(obj[0])+'_zx'
        i['verbose_name']=unicode(obj[1],'utf-8')+'/'+u'中心'
        i['width']=70
        header.append(i)
        j={}
        j['name']=str(obj[0])+'_gs'
        j['verbose_name']=unicode(obj[1],'utf-8')+'/'+u'公司'
        j['width']=70
        header.append(j)
    #初始化结构
    x = []
    a = get_season_list(begin_Date,end_Date)
    for i in a:
        j = {}
        j['season']=i
        j['total']=0
        for k in settings.PARA.TASK_TYPE:
            j[str(k[0])+'_zx']=0
            j[str(k[0])+'_gs']=0
        x.append(j)
    total={}
    total['season']=u'总计'
    total['total']=0
    for k in settings.PARA.TASK_TYPE:
        total[str(k[0])+'_zx']=0
        total[str(k[0])+'_gs']=0
    x.append(total)
    #生成数据
    task = get_model('tasks_details')
    sql = select([task.c.year,task.c.month,task.c.task_type,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),
                ],and_(task.c.year_month>=begin,
                task.c.year_month<=end,))\
                .group_by(task.c.year,task.c.month,task.c.task_type)
    sql_rs = sql.execute()
    
    for row in sql_rs:
        year,month,type,zx_sum,gs_sum = row
        if not type:
            continue
        
        for i in x:
            if i['season']==str(year)+'-'+str(season_map[month]):
                i[str(type)+'_zx']+=zx_sum
                i[str(type)+'_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
            if i['season']==u'总计':
                i[str(type)+'_zx']+=zx_sum                
                i[str(type)+'_gs']+=gs_sum                
                i['total']+=zx_sum
                i['total']+=gs_sum
    

    #展示        
    view =  SimpleListView(header, x, id='req_count_table',pagination=False)
    
    if 'download' in request.GET:
        return view.download('charts_resource_type_season_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#按照时间区间查询各系统工作量
@expose('/charts/resource/sys/date')
def charts_resource_sys_date():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    pageno = int(request.GET.get('pageno', 0))    
    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            

    task = get_model('tasks_details')
    sys = get_model('system')
    user_zx = request.user._kfzx_
    if request.user.department=='2':        
        sql = select([sys.c.sys_id,
                sys.c.name,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),
                func.sum(task.c.amount_inner_design),
                func.sum(task.c.amount_outer_design),
                func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
                func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),],
                and_(task.c.year_month>=begin,
                task.c.year_month<=end,
                sys.c.id==task.c.task_sys,
                task.c.kfzx==user_zx))\
                .group_by(sys.c.name)\
                .order_by(func.sum(task.c.amount_inner_input)+func.sum(task.c.amount_outer_input))
    
    else:
        sql = select([sys.c.sys_id,
                sys.c.name,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),
                func.sum(task.c.amount_inner_design),
                func.sum(task.c.amount_outer_design),
                func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
                func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),
                ],
                and_(task.c.year_month>=begin,
                task.c.year_month<=end,
                sys.c.id==task.c.task_sys))\
                .group_by(sys.c.name)\
                .order_by(func.sum(task.c.amount_inner_input)+func.sum(task.c.amount_outer_input))
    sql_rs = sql.execute()
    #print sql
    #生成数据
    j = [0]*(len(settings.PARA.resource_sys_date)-2)
    j.insert(0,u' ')
    j.insert(1,u'总计')
    x = []
    for row in sql_rs:
        name,sys_id,zx_sum,gs_sum,zx_sum_design,gs_sum_design,zx_sum_extra,gs_sum_extra = row
        row = list(row)
        row.insert(2,zx_sum+gs_sum)
        x.append(row)
        for i in range(2,len(row)):
            j[i]+=row[i]
    
    x.append(j)
    view =  SimpleListView(settings.PARA.resource_sys_date,
        x,id='req_count_table',table_width=True,pagination=False)
                
    
    if 'download' in request.GET:
        return view.download('charts_resource_sys_date_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#各系统工作量月报
@expose('/charts/resource/sys/month')
def charts_resource_sys_month():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    month_list = get_month_list(begin_Date,end_Date)
    month_list.reverse()
    sys = get_model('system')
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    task = get_model('tasks_details')
    #生成表头
    header = []
    header.append({'name':'sys_id','verbose_name':u'系统编号', 'width':70})
    header.append({'name':'sys','verbose_name':u'系统名称', 'width':200})
    header.append({'name':'type','verbose_name':u'系统属性', 'width':70})
    header.append({'name':'zx','verbose_name':u'所属中心', 'width':70})    
    #20110111根据中心人员需求，将合计字段分为中心合计和公司合计
    header.append({'name':'total_zx','verbose_name':u'月均/中心', 'width':70})
    header.append({'name':'total_gs','verbose_name':u'月均/公司', 'width':70})
    header.append({'name':'total','verbose_name':u'月均/合计', 'width':70})
    for obj in month_list:
        i={}
        i['name']=obj+'_zx'
        i['verbose_name']=obj+'/'+u'中心'
        i['width']=70
        header.append(i)
        j={}
        j['name']=obj+'_gs'
        j['verbose_name']=obj+'/'+u'公司'
        j['width']=70
        header.append(j)
        k={}
        k['name']=obj+'_hj'
        k['verbose_name']=obj+'/'+u'合计'
        k['width']=70
        header.append(k)
 
    condition = None
    #取非虚拟系统
    condition = (sys.c.sys_type!='2') & condition
    
    if request.user.department == '2':
        if request.user.is_gm:
            condition = (sys.c.kfzx==request.user._kfzx_) & condition
        else:
            ids = request.user.users_systems.ids()
            condition = sys.c.id.in_(ids) & condition
    
    #初始化结构
    x = []
    if request.user.department != '3':    
        for k in sys.filter(condition).all():
            if k.sys_type == '5':
                continue                
            j = {}        
            j['sys_id']=k.sys_id        
            j['sys']=k.id
            j['type']=k.sys_type
            j['zx']=k.kfzx.name
            j['total_zx']=0
            j['total_gs']=0
            j['total']=0
            for i in month_list:
                j[i+'_zx']=0
                j[i+'_gs']=0
                j[i+'_hj']=0
            x.append(j)
    if request.user.department != '2':    
        cs={}
        cs['sys_id']=u'北测'
        cs['sys']='--'
        cs['type']='--'
        cs['zx']='--'
        cs['total_zx']=0
        cs['total_gs']=0
        cs['total']=0
        for k in month_list:
            cs[k+'_zx']=0
            cs[k+'_gs']=0
            cs[k+'_hj']=0
        x.append(cs)

    total={}
    total['sys_id']=u'总计'
    total['sys']='--'
    total['type']='--'
    total['zx']='--'
    total['total_zx']=0
    total['total_gs']=0
    total['total']=0
    for k in month_list:
        total[k+'_zx']=0
        total[k+'_gs']=0
        total[k+'_hj']=0
    x.append(total)
    #生成数据
    condition = (sys.c.id == task.c.task_sys) & condition

    sql = select([task.c.year,task.c.month,task.c.task_sys,
            func.sum(task.c.amount_inner_input),
            func.sum(task.c.amount_outer_input),
            ],and_(task.c.year_month>=begin,
            task.c.year_month<=end,
            ~task.c.task_type.in_(['15','16','17','18']),condition))\
            .group_by(task.c.year,task.c.month,task.c.task_sys)
            #.order_by(func.sum(task.c.amount_inner_input)+func.sum(task.c.amount_outer_input))
    sql_cs = select([task.c.year,task.c.month,
            func.sum(task.c.amount_inner_input),
            func.sum(task.c.amount_outer_input),
            ],and_(task.c.year_month>=begin,
            task.c.year_month<=end,
            task.c.task_type.in_(['15','16','17','18'])))\
            .group_by(task.c.year,task.c.month)

    if request.user.department != '3':
        sql_rs = sql.execute()
        for row in sql_rs:
            year,month,zx,zx_sum,gs_sum = row
            for i in x:
                #20110111增加判断该任务所属系统是否为空判断，如果所属系统为空，则跳过该条统计记录
                if not zx:
                    continue
                #print 'zx',zx
                if i['sys']==zx:
                    i[str(year)+'-'+str(month)+'_zx']+=zx_sum
                    i[str(year)+'-'+str(month)+'_gs']+=gs_sum
                    i[str(year)+'-'+str(month)+'_hj']+=gs_sum+zx_sum                                                
                    i['total_zx']+=zx_sum
                    i['total_gs']+=gs_sum
                    i['total']=i['total']+zx_sum+gs_sum
                if i['sys_id']==u'总计':
                    i[str(year)+'-'+str(month)+'_zx']+=zx_sum                
                    i[str(year)+'-'+str(month)+'_gs']+=gs_sum                
                    i[str(year)+'-'+str(month)+'_hj']+=gs_sum+zx_sum                                                
                    i['total_zx']+=zx_sum
                    i['total_gs']+=gs_sum
                    i['total']=i['total']+zx_sum+gs_sum

    if request.user.department != '2':
        sql_cs_rs = sql_cs.execute()
        for row in sql_cs_rs:
            year,month,zx_sum,gs_sum = row
            for i in x:
                if i['sys_id']==u'北测':
                    i[str(year)+'-'+str(month)+'_zx']+=zx_sum
                    i[str(year)+'-'+str(month)+'_gs']+=gs_sum
                    i[str(year)+'-'+str(month)+'_hj']+=gs_sum+zx_sum                                                
                    i['total_zx']+=zx_sum
                    i['total_gs']+=gs_sum
                    i['total']=i['total']+zx_sum+gs_sum
                if i['sys_id']==u'总计':
                    i[str(year)+'-'+str(month)+'_zx']+=zx_sum                
                    i[str(year)+'-'+str(month)+'_gs']+=gs_sum                
                    i[str(year)+'-'+str(month)+'_hj']+=gs_sum+zx_sum                                                
                    i['total_zx']+=zx_sum
                    i['total_gs']+=gs_sum
                    i['total']=i['total']+zx_sum+gs_sum
    
    days = get_monthlist_days(month_list)
    for i in x:
        i['total']=str(round(i['total']/days,1))
        i['total_zx']=str(round(i['total_zx']/days,1))
        i['total_gs']=str(round(i['total_gs']/days,1))
        for j in month_list:
            days_month = get_month_days(j)
            i[j+'_zx']=str(round(i[j+'_zx']/days_month,1))
            i[j+'_gs']=str(round(i[j+'_gs']/days_month,1))
            i[j+'_hj']=str(round(i[j+'_hj']/days_month,1))
        
        if i['sys_id']!=u'总计' and i['sys_id']!=u'北测':
            i['sys']=sys.get(i['sys']).name 
            i['type']=dict(settings.PARA.SYSTEM_TYPE)[i['type']]
    
    #展示        
    view =  SimpleListView(header, x, id='req_count_table',table_width=True,pagination=False)

    if 'download' in request.GET:
        return view.download('charts_resource_sys_month_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#各系统工作量季报
@expose('/charts/resource/sys/season')
def charts_resource_sys_season():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    
    season_map = {1:1,2:1,3:1,4:2,5:2,6:2,7:3,8:3,9:3,10:4,11:4,12:4}
    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    sys = get_model('system')
    
    #生成表头
    header = []
    header.append({'name':'sys_id','verbose_name':'系统编号', 'width':70})
    header.append({'name':'sys','verbose_name':'系统名称', 'width':200})
    header.append({'name':'type','verbose_name':'系统属性', 'width':70})
    header.append({'name':'zx','verbose_name':'所属中心', 'width':70})    
    #20110111根据中心人员需求，将合计字段分为中心合计和公司合计
    header.append({'name':'total','verbose_name':'合计', 'width':70})
    header.append({'name':'total_zx','verbose_name':'中心合计', 'width':70})
    header.append({'name':'total_gs','verbose_name':'公司合计', 'width':70})    
    season_list = get_season_list(begin_Date,end_Date)
    season_list.reverse()
    for obj in season_list:
        i={}
        i['name']=obj+'_zx'
        i['verbose_name']=obj+'/'+u'中心'
        i['width']=70
        header.append(i)
        j={}
        j['name']=obj+'_gs'
        j['verbose_name']=obj+'/'+u'公司'
        j['width']=70
        header.append(j)
        k={}
        k['name']=obj+'_hj'
        k['verbose_name']=obj+'/'+u'合计'
        k['width']=70
        header.append(k)
    #初始化结构
    x = []
    for k in sys.all():
        j = {}
        j['sys_id']=k.sys_id        
        j['sys']=k.id
        j['type']=k.sys_type
        j['zx']=k.kfzx.name
        j['total']=0
        j['total_zx']=0
        j['total_gs']=0
        for i in season_list:
            j[i+'_zx']=0
            j[i+'_gs']=0
            j[i+'_hj']=0
        x.append(j)
    total={}
    
    total['sys_id']=u'总计'
    total['sys']='--'
    total['type']='--'
    total['zx']='--'
    total['total']=0
    total['total_zx']=0
    total['total_gs']=0
    for k in season_list:
        total[k+'_zx']=0
        total[k+'_gs']=0
        total[k+'_hj']=0        
    x.append(total)
    #生成数据
    user_zx = request.user._kfzx_
    task = get_model('tasks_details')
    if request.user.department=='2':        
        sql = select([task.c.year,task.c.month,task.c.task_sys,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),
                ],and_(task.c.year_month>=begin,
                task.c.year_month<=end,
                task.c.kfzx==user_zx))\
                .group_by(task.c.year,task.c.month,task.c.task_sys)\
                .order_by(func.sum(task.c.amount_inner_input)+func.sum(task.c.amount_outer_input))
    else:
        sql = select([task.c.year,task.c.month,task.c.task_sys,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),
                ],and_(task.c.year_month>=begin,
                task.c.year_month<=end,))\
                .group_by(task.c.year,task.c.month,task.c.task_sys)\
                .order_by(func.sum(task.c.amount_inner_input)+func.sum(task.c.amount_outer_input))
    sql_rs = sql.execute()
    
    for row in sql_rs:
        year,month,zx,zx_sum,gs_sum = row
        for i in x:
            #20110111增加判断该任务所属系统是否为空判断，如果所属系统为空，则跳过该条统计记录
            if not zx:
               continue
            if i['sys']==zx:
                i[str(year)+'-'+str(season_map[month])+'_zx']+=zx_sum
                i[str(year)+'-'+str(season_map[month])+'_gs']+=gs_sum
                i[str(year)+'-'+str(season_map[month])+'_hj']+=gs_sum+zx_sum                                                
                i['total_zx']+=zx_sum
                i['total_gs']+=gs_sum
                i['total']=i['total']+zx_sum+gs_sum
            if i['sys_id']==u'总计':
                i[str(year)+'-'+str(season_map[month])+'_zx']+=zx_sum                
                i[str(year)+'-'+str(season_map[month])+'_gs']+=gs_sum                
                i[str(year)+'-'+str(season_map[month])+'_hj']+=gs_sum+zx_sum                                                
                i['total_zx']+=zx_sum
                i['total_gs']+=gs_sum
                i['total']=i['total']+zx_sum+gs_sum
    xx = []
    for i in x:
        i['total']=str(round(i['total']/66,1))
        i['total_zx']=str(round(i['total_zx']/66,1))
        i['total_gs']=str(round(i['total_gs']/66,1))
        for j in season_list:
            i[j+'_zx']=str(round(i[j+'_zx']/66,1))
            i[j+'_gs']=str(round(i[j+'_gs']/66,1))
            i[j+'_hj']=str(round(i[j+'_hj']/66,1))
        
        if i['sys_id']!=u'总计':
            i['sys']=sys.get(i['sys']).name 
        xx.append(i)                
    
    #展示        
    view =  SimpleListView(header, xx, id='req_count_table',table_width=True,pagination=False)
    
    if 'download' in request.GET:
        return view.download('charts_resource_sys_season_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

    
#按照时间区间查询各条线需求消耗工作量
@expose('/charts/resource/tx/date')
def charts_resource_tx_date():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    
    task = get_model('tasks_details')
    tx = get_model('tx')
    #01-05类任务，可对应到条线
    sql_develop = select([tx.c.name,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),
                func.sum(task.c.amount_inner_design),
                func.sum(task.c.amount_outer_design),
                func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
                func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),],
                and_(task.c.year_month>=begin,
                task.c.year_month<=end,
                task.c.task_type.in_(['01','02','03','04','05','23']),
                tx.c.id==task.c.tx))\
                .group_by(tx.c.name)\
                .order_by(func.sum(task.c.amount_inner_input)+func.sum(task.c.amount_outer_input))
    sql_rs = sql_develop.execute()
    
    #其他任务，按类型分类
    sql_other = select([task.c.task_type,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),
                func.sum(task.c.amount_inner_design),
                func.sum(task.c.amount_outer_design),
                func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
                func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),],
                and_(task.c.year_month>=begin,
                task.c.year_month<=end,
                ~task.c.task_type.in_(['01','02','03','04','05','23'])))\
                .group_by(task.c.task_type)
    sql_other_rs = sql_other.execute()
    
    #生成数据
    x = []
    j = [0]*len(settings.PARA.resource_tx_date)
    j.insert(0,u'总计')
    for row in sql_rs:
        name,zx_sum,gs_sum,zx_sum_design,gs_sum_design,zx_sum_extra,gs_sum_extra = row
        row = list(row)
        row.insert(1,zx_sum+gs_sum)
        for i in range(1,len(row)):
            j[i]+=row[i]
        x.append(row)
    
    t = []
    
    for row in sql_other_rs:
        name,zx_sum,gs_sum,zx_sum_design,gs_sum_design,zx_sum_extra,gs_sum_extra = row        
        row = list(row)
        row.insert(1,zx_sum+gs_sum)
        k = 0
        for r in t:
            if r[0]==settings.PARA.task_type_map[name][1]:   
                k = 1         
                for i in range(1,len(row)):
                    r[i]+=row[i]
                    j[i]+=row[i]
                break
        if k!=1:
            s = [0]*len(settings.PARA.resource_tx_date)
            s[0]=settings.PARA.task_type_map[name][1]
            for i in range(1,len(row)):
                s[i]+=row[i]
                j[i]+=row[i]
            t.append(s)
    x += t
    x.append(j)
           
    view =  SimpleListView(settings.PARA.resource_tx_date,x,
        id='req_count_table',pagination=False)
                
    
    if 'download' in request.GET:
        return view.download('charts_resource_tx_date_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#各条线涉及需求工作量月报
@expose('/charts/resource/tx/month')
def charts_resource_tx_month():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    tx = get_model('tx')
    #生成表头
    header = []
    header.append({'name':'month','verbose_name':u'月份', 'width':70})
    header.append({'name':'total','verbose_name':u'合计', 'width':70})
    for obj in tx.all():
        i={}
        i['name']=str(obj.id)+'_zx'
        #obj.name = unicode(obj.name,'sys.stdin.encoding')
        i['verbose_name']=obj.name +u'/'+u'中心'
        #i['verbose_name']=obj.name +'/'+'zx'
        #print obj.name
        #print i,'vvv',i['verbose_name']        
        i['width']=70
        header.append(i)
        j={}
        j['name']=str(obj.id)+'_gs'
        j['verbose_name']=obj.name +u'/'+u'公司'
        #j['verbose_name']=obj.name +'/'+'gs'
        #print obj.name
        #print j,'vvv',j['verbose_name']
        j['width']=70
        header.append(j)
    #print 'xx'
    header.append({'name':'yw_zx','verbose_name':u'运维相关/中心', 'width':70})
    header.append({'name':'yw_gs','verbose_name':u'运维相关/公司', 'width':70})
    header.append({'name':'cs_zx','verbose_name':u'测试相关/中心', 'width':70})
    header.append({'name':'cs_gs','verbose_name':u'测试相关/公司', 'width':70})
    header.append({'name':'gg_zx','verbose_name':u'公共任务/中心', 'width':70})
    header.append({'name':'gg_gs','verbose_name':u'公共任务/公司', 'width':70})
    header.append({'name':'qt_zx','verbose_name':u'其他任务/中心', 'width':70})
    header.append({'name':'qt_gs','verbose_name':u'其他任务/公司', 'width':70})
    #初始化结构
    x = []
    a = get_month_list(begin_Date,end_Date)
    for i in a:
        j = {}
        j['month']=i
        j['total']=0
        for k in tx.all():
            j[str(k.id)+'_zx']=0
            j[str(k.id)+'_gs']=0
        j['yw_zx']=0
        j['yw_gs']=0
        j['cs_zx']=0
        j['cs_gs']=0
        j['gg_zx']=0
        j['gg_gs']=0
        j['qt_zx']=0
        j['qt_gs']=0
        x.append(j)
    total={}
    total['month']=u'总计'
    total['total']=0
    for k in tx.all():
        total[str(k.id)+'_zx']=0
        total[str(k.id)+'_gs']=0
    total['yw_zx']=0
    total['yw_gs']=0
    total['cs_zx']=0
    total['cs_gs']=0
    total['gg_zx']=0
    total['gg_gs']=0
    total['qt_zx']=0
    total['qt_gs']=0
    x.append(total)

    #生成数据
    #01-05类任务，可对应到条线
    task = get_model('tasks_details')
    sql_develop = select([task.c.year,task.c.month,task.c.tx,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),],
                and_(task.c.year_month>=begin,
                task.c.year_month<=end,
                task.c.task_type.in_(['01','02','03','04','05','23']),
                tx.c.id==task.c.tx))\
                .group_by(task.c.year,task.c.month,task.c.tx)
                
    sql_rs = sql_develop.execute()
    
    #其他任务，按类型分类
    sql_other = select([task.c.year,task.c.month,task.c.task_type,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),],
                and_(task.c.year_month>=begin,
                task.c.year_month<=end,
                ~task.c.task_type.in_(['01','02','03','04','05','23'])))\
                .group_by(task.c.year,task.c.month,task.c.task_type)
    sql_other_rs = sql_other.execute()
    
    for row in sql_rs:
        year,month,zx,zx_sum,gs_sum = row
        for i in x:
            if i['month']==str(year)+'-'+str(month):
                i[str(zx)+'_zx']=zx_sum
                i[str(zx)+'_gs']=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
            if i['month']==u'总计':
                i[str(zx)+'_zx']+=zx_sum                
                i[str(zx)+'_gs']+=gs_sum                
                i['total']+=zx_sum
                i['total']+=gs_sum
    for row in sql_other_rs:
        year,month,type,zx_sum,gs_sum = row
        for i in x:
            if i['month']==str(year)+'-'+str(month):
                i[settings.PARA.task_type_map[type][0]+'_zx']+=zx_sum
                i[settings.PARA.task_type_map[type][0]+'_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
            if i['month']==u'总计':
                i[settings.PARA.task_type_map[type][0]+'_zx']+=zx_sum                
                i[settings.PARA.task_type_map[type][0]+'_gs']+=gs_sum                
                i['total']+=zx_sum
                i['total']+=gs_sum
    
                    
    #展示        
    view =  SimpleListView(header, x, id='req_count_table',table_width=True,pagination=False)

    if 'download' in request.GET:
        return view.download('charts_resource_tx_month_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#各条线涉及需求工作量季报
@expose('/charts/resource/tx/season')
def charts_resource_tx_season():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    tx = get_model('tx')
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    season_map = {1:1,2:1,3:1,4:2,5:2,6:2,7:3,8:3,9:3,10:4,11:4,12:4}

    #生成表头
    header = []
    header.append({'name':'season','verbose_name':'季度', 'width':70})
    header.append({'name':'total','verbose_name':'合计', 'width':70})
    for obj in tx.all():
        i={}
        i['name']=str(obj.id)+'_zx'
        i['verbose_name']=obj.name+'/'+u'中心'
        i['width']=70
        header.append(i)
        j={}
        j['name']=str(obj.id)+'_gs'
        j['verbose_name']=obj.name+'/'+u'公司'
        j['width']=70
        header.append(j)
    header.append({'name':'yw_zx','verbose_name':'运维相关/中心', 'width':70})
    header.append({'name':'yw_gs','verbose_name':'运维相关/公司', 'width':70})
    header.append({'name':'cs_zx','verbose_name':'测试相关/中心', 'width':70})
    header.append({'name':'cs_gs','verbose_name':'测试相关/公司', 'width':70})
    header.append({'name':'gg_zx','verbose_name':'公共任务/中心', 'width':70})
    header.append({'name':'gg_gs','verbose_name':'公共任务/公司', 'width':70})
    header.append({'name':'qt_zx','verbose_name':'其他任务/中心', 'width':70})
    header.append({'name':'qt_gs','verbose_name':'其他任务/公司', 'width':70})
    #初始化结构
    x = []
    a = get_season_list(begin_Date,end_Date)
    for i in a:
        j = {}
        j['season']=i
        j['total']=0
        for k in tx.all():
            j[str(k.id)+'_zx']=0
            j[str(k.id)+'_gs']=0
        j['yw_zx']=0
        j['yw_gs']=0
        j['cs_zx']=0
        j['cs_gs']=0
        j['gg_zx']=0
        j['gg_gs']=0
        j['qt_zx']=0
        j['qt_gs']=0
        x.append(j)
    total={}
    total['season']=u'总计'
    total['total']=0
    for k in tx.all():
        total[str(k.id)+'_zx']=0
        total[str(k.id)+'_gs']=0
    total['yw_zx']=0
    total['yw_gs']=0
    total['cs_zx']=0
    total['cs_gs']=0
    total['gg_zx']=0
    total['gg_gs']=0
    total['qt_zx']=0
    total['qt_gs']=0
    x.append(total)

    #生成数据
    #01-05类任务，可对应到条线
    task = get_model('tasks_details')
    sql_develop = select([task.c.year,task.c.month,task.c.tx,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),],
                and_(task.c.year_month>=begin,
                task.c.year_month<=end,
                task.c.task_type.in_(['01','02','03','04','05','23']),
                tx.c.id==task.c.tx))\
                .group_by(task.c.year,task.c.month,task.c.tx)
                
    sql_rs = sql_develop.execute()
    
    #其他任务，按类型分类
    sql_other = select([task.c.year,task.c.month,task.c.task_type,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),],
                and_(task.c.year_month>=begin,
                task.c.year_month<=end,
                ~task.c.task_type.in_(['01','02','03','04','05','23'])))\
                .group_by(task.c.year,task.c.month,task.c.task_type)
    sql_other_rs = sql_other.execute()
    
    for row in sql_rs:
        year,month,zx,zx_sum,gs_sum = row
        for i in x:
            if i['season']==str(year)+'-'+str(season_map[month]):
                i[str(zx)+'_zx']+=zx_sum
                i[str(zx)+'_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
            if i['season']==u'总计':
                i[str(zx)+'_zx']+=zx_sum                
                i[str(zx)+'_gs']+=gs_sum                
                i['total']+=zx_sum
                i['total']+=gs_sum
    for row in sql_other_rs:
        year,month,type,zx_sum,gs_sum = row
        for i in x:
            if i['season']==str(year)+'-'+str(season_map[month]):
                i[settings.PARA.task_type_map[type][0]+'_zx']+=zx_sum
                i[settings.PARA.task_type_map[type][0]+'_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
            if i['season']==u'总计':
                i[settings.PARA.task_type_map[type][0]+'_zx']+=zx_sum                
                i[settings.PARA.task_type_map[type][0]+'_gs']+=gs_sum                
                i['total']+=zx_sum
                i['total']+=gs_sum
    
                    
    #展示        
    view =  SimpleListView(header, x, id='req_count_table',pagination=False)

    if 'download' in request.GET:
        return view.download('charts_resource_tx_season_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#按照时间区间查询各阶段工作量
@expose('/charts/resource/stage/date')
def charts_resource_stage_date():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    
    task = get_model('tasks_details')
    user_zx = request.user._kfzx_
    if request.user.department=='2':
        sql = select([task.c.stage,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),
                    func.sum(task.c.amount_inner_design),
                    func.sum(task.c.amount_outer_design),
                    func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
                    func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),],
                    and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type.in_(['01','02','03','04']),
                    ~task.c.stage.in_(['00','FN']),
                    task.c.kfzx==user_zx))\
                    .group_by(task.c.stage)\
                    .order_by(task.c.stage)
        sql_ky = select([task.c.task_type,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),
                    func.sum(task.c.amount_inner_design),
                    func.sum(task.c.amount_outer_design),
                    func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
                    func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),],
                    and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type=='05',
                    task.c.kfzx==user_zx))\
                    .group_by(task.c.task_type)
    else:
        sql = select([task.c.stage,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),
                    func.sum(task.c.amount_inner_design),
                    func.sum(task.c.amount_outer_design),
                    func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
                    func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),],
                    and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type.in_(['01','02','03','04']),
                    ~task.c.stage.in_(['00','FN'])))\
                    .group_by(task.c.stage)\
                    .order_by(task.c.stage)
        sql_ky = select([task.c.task_type,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),
                    func.sum(task.c.amount_inner_design),
                    func.sum(task.c.amount_outer_design),
                    func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
                    func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),],
                    and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type=='05'))\
                    .group_by(task.c.task_type)
        
    sql_rs = sql.execute()
    sql_ky_rs = sql_ky.execute()
    
    #生成数据
    j = [0]*len(settings.PARA.resource_stage_date)
    j.insert(0,u'总计')
    x = []
    map_stage = dict(settings.PARA.TASK_STAGE)
    for row in sql_rs:
        name,zx_sum,gs_sum,zx_sum_design,gs_sum_design,zx_sum_extra,gs_sum_extra = row
        row = list(row)
        row[0]= map_stage[name]
        row.insert(1,zx_sum+gs_sum)
        x.append(row)
        for i in range(1,len(row)):
            j[i]+=row[i]
    #插入可研阶段数据
    for row in sql_ky_rs:
        name,zx_sum,gs_sum,zx_sum_design,gs_sum_design,zx_sum_extra,gs_sum_extra = row
        row = list(row)
        row[0]='可研'
        row.insert(1,zx_sum+gs_sum)
        x.insert(0,row)
        for i in range(1,len(row)):
            j[i]+=row[i]
    #插入汇总数据
    x.append(j)

    view =  SimpleListView(settings.PARA.resource_stage_date,x,
        id='req_count_table',pagination=False)
                
    
    if 'download' in request.GET:
        return view.download('charts_resource_stage_date_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#各阶段工作量月报
@expose('/charts/resource/stage/month')
def charts_resource_stage_month():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    #生成表头
    header = []
    header.append({'name':'month','verbose_name':'月份', 'width':70})
    header.append({'name':'total','verbose_name':'合计', 'width':70})
    header.append({'name':'ky_zx','verbose_name':'可研/中心', 'width':70})
    header.append({'name':'ky_gs','verbose_name':'可研/公司', 'width':70})
    for obj in settings.PARA.TASK_STAGE:
        if obj[0] not in ['00','FN']:            
            i={}
            i['name']=obj[0]+'_zx'
            i['verbose_name']=obj[1]+'/'+'中心'
            i['width']=70
            header.append(i)
            j={}
            j['name']=obj[0]+'_gs'
            j['verbose_name']=obj[1]+'/'+'公司'
            j['width']=70
            header.append(j)
    #初始化结构
    x = []
    a = get_month_list(begin_Date,end_Date)
    for i in a:
        j = {}
        j['month']=i
        j['total']=0
        j['ky_zx']=0
        j['ky_gs']=0
        for k in settings.PARA.TASK_STAGE:
            if k[0] not in ['00','FN']:                
                j[k[0]+'_zx']=0
                j[k[0]+'_gs']=0
        x.append(j)
    total={}
    total['month']=u'总计'
    total['total']=0
    total['ky_zx']=0
    total['ky_gs']=0    
    for k in settings.PARA.TASK_STAGE:
        if k[0] not in ['00','FN']: 
            total[k[0]+'_zx']=0
            total[k[0]+'_gs']=0
    x.append(total)
    #生成数据
    task = get_model('tasks_details')
    user_zx = request.user._kfzx_
    if request.user.department=='2':
        sql = select([task.c.year,task.c.month,task.c.stage,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),
                    ],and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type.in_(['01','02','03','04']),
                    ~task.c.stage.in_(['00','FN']),
                    task.c.kfzx==user_zx))\
                    .group_by(task.c.year,task.c.month,task.c.stage)
        sql_ky = select([task.c.year,task.c.month,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),],
                    and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type=='05',
                    task.c.kfzx==user_zx))\
                    .group_by(task.c.year,task.c.month)
    else:
        sql = select([task.c.year,task.c.month,task.c.stage,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),
                    ],and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type.in_(['01','02','03','04']),
                    ~task.c.stage.in_(['00','FN'])))\
                    .group_by(task.c.year,task.c.month,task.c.stage)
        sql_ky = select([task.c.year,task.c.month,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),],
                    and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type=='05'))\
                    .group_by(task.c.year,task.c.month)
        
    sql_ky_rs = sql_ky.execute()
    sql_rs = sql.execute()

    
    for row in sql_rs:
        year,month,zx,zx_sum,gs_sum = row
        for i in x:
            if i['month']==str(year)+'-'+str(month):
                i[str(zx)+'_zx']=zx_sum
                i[str(zx)+'_gs']=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
            if i['month']==u'总计':
                i[str(zx)+'_zx']+=zx_sum                
                i[str(zx)+'_gs']+=gs_sum                
                i['total']+=zx_sum
                i['total']+=gs_sum

    for row in sql_ky_rs:
        year,month,zx_sum,gs_sum = row
        for i in x:
            if i['month']==str(year)+'-'+str(month):
                i['ky_zx']=zx_sum
                i['ky_gs']=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
            if i['month']==u'总计':
                i['ky_zx']+=zx_sum                
                i['ky_gs']+=gs_sum                
                i['total']+=zx_sum
                i['total']+=gs_sum
    
    #展示        
    view =  SimpleListView(header, x, id='req_count_table',pagination=False)

    if 'download' in request.GET:
        return view.download('charts_resource_stage_month_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#各阶段工作量季报
@expose('/charts/resource/stage/season')
def charts_resource_stage_season():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    season_map = {1:1,2:1,3:1,4:2,5:2,6:2,7:3,8:3,9:3,10:4,11:4,12:4}
    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    #生成表头
    header = []
    header.append({'name':'season','verbose_name':'季度', 'width':70})
    header.append({'name':'total','verbose_name':'合计', 'width':70})
    header.append({'name':'ky_zx','verbose_name':'可研/中心', 'width':70})
    header.append({'name':'ky_gs','verbose_name':'可研/公司', 'width':70})
    for obj in settings.PARA.TASK_STAGE:
        if obj[0] not in ['00','FN']:            
            i={}
            i['name']=obj[0]+'_zx'
            i['verbose_name']=obj[1]+'/'+'中心'
            i['width']=70
            header.append(i)
            j={}
            j['name']=obj[0]+'_gs'
            j['verbose_name']=obj[1]+'/'+'公司'
            j['width']=70
            header.append(j)
    #初始化结构
    x = []
    a = get_season_list(begin_Date,end_Date)
    for i in a:
        j = {}
        j['season']=i
        j['total']=0
        j['ky_zx']=0
        j['ky_gs']=0
        for k in settings.PARA.TASK_STAGE:
            if k[0] not in ['00','FN']:                
                j[k[0]+'_zx']=0
                j[k[0]+'_gs']=0
        x.append(j)
    total={}
    total['season']=u'总计'
    total['total']=0
    total['ky_zx']=0
    total['ky_gs']=0    
    for k in settings.PARA.TASK_STAGE:
        if k[0] not in ['00','FN']: 
            total[k[0]+'_zx']=0
            total[k[0]+'_gs']=0
    x.append(total)
    #生成数据
    task = get_model('tasks_details')
    user_zx = request.user._kfzx_
    if request.user.department=='2':
        sql = select([task.c.year,task.c.month,task.c.stage,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),
                    ],and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type.in_(['01','02','03','04']),
                    ~task.c.stage.in_(['00','FN']),
                    task.c.kfzx==user_zx))\
                    .group_by(task.c.year,task.c.month,task.c.stage)
        sql_ky = select([task.c.year,task.c.month,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),],
                    and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type=='05',
                    task.c.kfzx==user_zx))\
                    .group_by(task.c.year,task.c.month)
    else:
        sql = select([task.c.year,task.c.month,task.c.stage,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),
                    ],and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type.in_(['01','02','03','04']),
                    ~task.c.stage.in_(['00','FN'])))\
                    .group_by(task.c.year,task.c.month,task.c.stage)
        sql_ky = select([task.c.year,task.c.month,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),],
                    and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type=='05'))\
                    .group_by(task.c.year,task.c.month)
        
    sql_ky_rs = sql_ky.execute()
    sql_rs = sql.execute()
    
    
    for row in sql_rs:
        year,month,zx,zx_sum,gs_sum = row
        for i in x:
            if i['season']==str(year)+'-'+str(season_map[month]):
                i[str(zx)+'_zx']+=zx_sum
                i[str(zx)+'_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
            if i['season']==u'总计':
                i[str(zx)+'_zx']+=zx_sum                
                i[str(zx)+'_gs']+=gs_sum                
                i['total']+=zx_sum
                i['total']+=gs_sum
    
    for row in sql_ky_rs:
        year,month,zx_sum,gs_sum = row
        for i in x:
            if i['season']==str(year)+'-'+str(season_map[month]):
                i['ky_zx']+=zx_sum
                i['ky_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
            if i['season']==u'总计':
                i['ky_zx']+=zx_sum                
                i['ky_gs']+=gs_sum                
                i['total']+=zx_sum
                i['total']+=gs_sum
    
    #展示        
    view =  SimpleListView(header, x, id='req_count_table',pagination=False)

    if 'download' in request.GET:
        return view.download('charts_resource_stage_season_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#管理类报表-中心资源投入月报
@expose('/charts/manage/resource/zx/month')
def charts_manage_resource_zx_month():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from query import create_manage_resource_zx_month_query
    from uliweb.core.html import Tag
    from decimal import Decimal
    
    query = create_manage_resource_zx_month_query(url_for(charts_manage_resource_zx_month))
    c = query.run()
    
    sel_yearmonth = None
    if c.get('year') and c.get('month'):
        year_list = dict(settings.PARA.YEAR_LIST)
        month_list = dict(settings.PARA.MONTH_LIST)
        sel_yearmonth = int(year_list[int(c['year'])])*100+int(month_list[int(c['month'])])
    
    #当月工作日
    days = float(get_month_days(str(sel_yearmonth)))
    
    sys = get_model('system')
    zx = get_model('kfzx')
    task = get_model('tasks_details')
    resource = get_model('resourcehis')
    human = get_model('human')
    department = get_model('department')
    #生成表头
    header = []
    header.append({'name':'zx','verbose_name':u'中心', 'width':70})
    header.append({'name':'zx_workload','verbose_name':u'中心人员资源投入情况/中心工作量|考勤时长', 'width':130})
    header.append({'name':'zx_dev_rate','verbose_name':u'中心人员资源投入情况/开发工作量占比', 'width':120})    
    header.append({'name':'zx_num','verbose_name':u'中心人员资源投入情况/中心报工人数(含借调)', 'width':120})
    header.append({'name':'zx_qd','verbose_name':u'中心人员资源投入情况/中心人员工作强度', 'width':100})
    header.append({'name':'zx_bg_rate','verbose_name':u'中心人员资源投入情况/中心人员分配率', 'width':100})    
    header.append({'name':'gs_workload','verbose_name':u'公司人员资源投入情况/公司工作量|考勤时长', 'width':130})
    header.append({'name':'gs_dev_rate','verbose_name':u'公司人员资源投入情况/开发工作量占比', 'width':120})    
    header.append({'name':'gs_num','verbose_name':u'公司人员资源投入情况/公司报工人数', 'width':90})
    header.append({'name':'gs_qd','verbose_name':u'公司人员资源投入情况/公司人员工作强度', 'width':100})
    header.append({'name':'total_workload','verbose_name':u'合计/工作量合计', 'width':80})
    header.append({'name':'total_num','verbose_name':u'合计/人数合计', 'width':70})
    
    condition = None
#    condition = (zx.c.type == '1') & condition
    if request.user.department == '2':
        if request.user.is_gm:
            condition = (zx.c.id==request.user._kfzx_) & condition
        else:
            #非中心管理人员不能看到相关数据
            condition = (zx.c.id == 1000) & condition
    
    #初始化结构
    x = []
    if request.user.department != '3':    
        for k in zx.filter((zx.c.type == '1') & condition).all():
            j = {}      
            j['id']=k.id  
            j['zx']=k.id
            j['zx_workload']=0
            j['zx_dev_rate']=0
            j['zx_num']=0
            j['zx_qd']=0
            j['zx_bg']= 0
            j['zx_bg_rate']=0
            j['zx_bg_total'] = human.count((human.c.type == '3')&(human.c.department == department.c.code)&(department.c.kfzx==k.id)&(human.c.delete_flag!='1'))
            j['gs_workload']=0
            j['gs_dev_rate']=0
            j['gs_num']=0
            j['gs_qd']=0
            j['total_workload']=0
            j['total_num']=0
            j['zx_worktime']=0
            j['gs_worktime']=0
            x.append(j)
    if request.user.department != '2':    
        cs = {}
        cs['id']=u'北测'
        cs['zx']=u'北测'
        cs['zx_workload']=0
        cs['zx_dev_rate']=0
        cs['zx_num']=0
        cs['zx_qd']=0
        cs['zx_bg']=0
        cs['zx_bg_rate']=0
        cs['zx_bg_total']=0
        cs['gs_workload']=0
        cs['gs_dev_rate']=0
        cs['gs_num']=0
        cs['gs_qd']=0
        cs['total_workload']=0
        cs['total_num']=0
        cs['zx_worktime']=0
        cs['gs_worktime']=0        
        x.append(cs)
    if request.user.department != '2' and request.user.department != '3':
        total={}
        total['id']=u'总计'
        total['zx']=u'总计'
        total['zx_workload']=0
        total['zx_dev_rate']=0
        total['zx_num']=0
        total['zx_qd']=0
        total['zx_bg']=0
        total['zx_bg_rate']=0
        total['zx_bg_total'] = human.count((human.c.type == '3')&(human.c.department == department.c.code)&(department.c.kfzx!=None)&(human.c.delete_flag!='1'))
        total['gs_workload']=0
        total['gs_dev_rate']=0
        total['gs_num']=0
        total['gs_qd']=0
        total['total_workload']=0
        total['total_num']=0
        x.append(total)
    #生成数据
        
    sql = select([task.c.kfzx,
            func.sum(task.c.amount_inner_input),
            func.sum(task.c.amount_outer_input),
            ],and_(task.c.year_month==sel_yearmonth,
            ~task.c.task_type.in_(['15','16','17','18']),condition))\
            .group_by(task.c.kfzx)
    
    sql_dev = select([task.c.kfzx,
            func.sum(task.c.amount_inner_input),
            func.sum(task.c.amount_outer_input),
            ],and_(task.c.year_month==sel_yearmonth,
            task.c.task_type.in_(['01','02','03','04','05','06']),condition))\
            .group_by(task.c.kfzx)

    #投入资源情况，排除9090专业化测试系统，作为测试中心人员统计
    sql_res = select([resource.c.kfzx,resource.c.type,
            func.sum(resource.c.workload*resource.c.workday/days),
            func.sum(resource.c.worktime/days),
            ],and_(resource.c.yearmon==sel_yearmonth,
            resource.c.system!=164))\
            .group_by(resource.c.kfzx,resource.c.type)
#    print sql_res
    sql_cs = select([task.c.kfzx,
            func.sum(task.c.amount_inner_input),
            func.sum(task.c.amount_outer_input),
            ],and_(task.c.year_month==sel_yearmonth,
            task.c.task_type.in_(['15','16','17','18']),condition))\
            .group_by(task.c.kfzx)
    sql_cs_res = select([resource.c.kfzx,resource.c.type,
            func.sum(resource.c.workload*resource.c.workday/days),
            func.sum(resource.c.worktime/days),
            ],and_(resource.c.yearmon==sel_yearmonth,
            resource.c.system==164))\
            .group_by(resource.c.kfzx,resource.c.type)
    #报工率统计,包含中心借出人员
    sql_bg = select([department.c.kfzx,resource.c.type,
            func.sum(resource.c.workload),
            ],and_(resource.c.yearmon==sel_yearmonth,
            resource.c.user==human.c.login_name,
            human.c.department==department.c.code))\
            .group_by(department.c.kfzx,resource.c.type)
#    print sql_bg
    if request.user.department != '3':
        sql_rs = sql.execute()
        for row in sql_rs:
            kfzx,zx_sum,gs_sum = row
            for i in x:
                #20110111增加判断该任务所属系统是否为空判断，如果所属系统为空，则跳过该条统计记录
                if not kfzx:
                    continue
                #print 'zx',zx
                if i['zx']==kfzx:
                    i['zx_workload']+=zx_sum
                    i['gs_workload']+=gs_sum
                    i['total_workload']+=gs_sum+zx_sum                                                
                if i['zx']==u'总计':
                    i['zx_workload']+=zx_sum
                    i['gs_workload']+=gs_sum
                    i['total_workload']+=gs_sum+zx_sum                                                
        sql_dev_rs = sql_dev.execute()
        for row in sql_dev_rs:
            kfzx,zx_sum,gs_sum = row
            for i in x:
                #20110111增加判断该任务所属系统是否为空判断，如果所属系统为空，则跳过该条统计记录
                if not kfzx:
                    continue
                #print 'zx',zx
                if i['zx']==kfzx:
                    i['zx_dev_rate']+=zx_sum
                    i['gs_dev_rate']+=gs_sum                                                  
                if i['zx']==u'总计':
                    i['zx_dev_rate']+=zx_sum
                    i['gs_dev_rate']+=gs_sum
        
        sql_res_rs = sql_res.execute()
        for row in sql_res_rs:
            kfzx,type,num,worktime = row
            for i in x:
                #20110111增加判断该任务所属系统是否为空判断，如果所属系统为空，则跳过该条统计记录
                if not kfzx:
                    continue
                #中心人员包括中心人员和借调人员
                if i['zx']==kfzx and (type == '1' or type == '3'):
                    i['zx_num']+=num
                    i['zx_worktime']+=worktime
                    i['total_num']+=num
                if i['zx']==kfzx and type == '2':
                    i['gs_num']+=num
                    i['gs_worktime']+=worktime
                    i['total_num']+=num
                
                if i['zx']==u'总计' and (type == '1' or type == '3'):
                    i['zx_num']+=num
                    i['total_num']+=num                                               
                if i['zx']==u'总计' and type == '2':
                    i['gs_num']+=num
                    i['total_num']+=num                                                
        
        sql_bg_rs = sql_bg.execute()
        for row in sql_bg_rs:
            kfzx,type,num = row
            for i in x:
                #20110111增加判断该任务所属系统是否为空判断，如果所属系统为空，则跳过该条统计记录
                if not kfzx:
                    continue
                if i['zx']==kfzx and (type == '3'):
                    i['zx_bg']+=num
                
                if i['zx']==u'总计' and (type == '3'):
                    i['zx_bg']+=num
        
    if request.user.department != '2':
        sql_cs_rs = sql_cs.execute()
        for row in sql_cs_rs:
            kfzx,zx_sum,gs_sum = row
            for i in x:
                if i['zx']==u'北测':
                    i['zx_workload']+=zx_sum
                    i['gs_workload']+=gs_sum
                    i['total_workload']+=gs_sum+zx_sum
                if i['zx']==u'总计':
                    i['zx_workload']+=zx_sum
                    i['gs_workload']+=gs_sum
                    i['total_workload']+=gs_sum+zx_sum
  
        sql_cs_res_rs = sql_cs_res.execute()
        for row in sql_cs_res_rs:
            kfzx,type,num,worktime = row
            for i in x:
                #20110111增加判断该任务所属系统是否为空判断，如果所属系统为空，则跳过该条统计记录
                if not kfzx:
                    continue
                #中心人员包括中心人员和借调人员
                if i['zx']==u'北测' and (type == '1' or type == '3'):
                    i['zx_num']+=num
                    i['zx_worktime']+=worktime
                    i['total_num']+=num
                if i['zx']==u'北测' and type == '2':
                    i['gs_num']+=num
                    i['gs_worktime']+=worktime
                    i['total_num']+=num
                
                if i['zx']==u'总计' and (type == '1' or type == '3'):
                    i['zx_num']+=num
                    i['total_num']+=num                                               
                if i['zx']==u'总计' and type == '2':
                    i['gs_num']+=num
                    i['total_num']+=num                                                
        
    
    for i in x:
        if i['zx_workload']>0:
            i['zx_dev_rate'] = str(round(float(i['zx_dev_rate'])/float(i['zx_workload']),4)*100)+'%'
        if i['gs_workload']>0:
            i['gs_dev_rate'] = str(round(float(i['gs_dev_rate'])/float(i['gs_workload']),4)*100)+'%'
        if i['zx_num']>0:
            i['zx_qd'] = i['zx_workload']/days/float(i['zx_num'])
        if i['gs_num']>0:
            i['gs_qd'] = i['gs_workload']/days/float(i['gs_num'])
        i['zx_workload']=str(round(i['zx_workload']/days,1))
        i['gs_workload']=str(round(i['gs_workload']/days,1))
        i['zx_num']=str(round(i['zx_num'],1))
        i['gs_num']=str(round(i['gs_num'],1))
        i['zx_qd']=str(round(i['zx_qd'],1))
        i['gs_qd']=str(round(i['gs_qd'],1))
        if i['zx_bg_total']>0:            
            i['zx_bg_rate']=str(round(float(i['zx_bg'])/float(i['zx_bg_total']),4)*100)+'%'
        else:
            i['zx_bg_rate']='0.0%'
        i['total_num']=str(round(i['total_num'],1))        
        i['total_workload']=str(round(i['total_workload']/days,1))
        
        if i['zx']!=u'总计' and i['zx']!=u'北测':
            i['zx']=zx.get(i['zx']).name 


    def zx_workload(value, obj):
        
        if obj['id']==u'总计':
            return value
        
        if round(obj['zx_worktime'],1)<float(value):
            return "<font color='red'>"+str(value)+"/"+str(round(obj['zx_worktime'],1))+"</font>"
        else:
            return str(value)+"/"+str(round(obj['zx_worktime'],1))

    def gs_workload(value, obj):
        if obj['id']==u'总计':
            return value
        if round(obj['gs_worktime'],1)<float(value):
            return "<font color='red'>"+str(value)+"/"+str(round(obj['gs_worktime'],1))+"</font>"
        else:
            return str(value)+"/"+str(round(obj['gs_worktime'],1))
        
    def zx_num(value, obj):
        if obj['id']==u'总计':
            return str(Tag('a', value, href=url_for('resource.views.resourcehis_usage_list',
                yearmon=sel_yearmonth, type=1),target='_blank'))
        elif obj['id']==u'北测':
            return str(Tag('a', value, href=url_for('resource.views.resourcehis_usage_list',
                yearmon=sel_yearmonth, type=1, system=164),target='_blank'))
        else:
            return str(Tag('a', value, href=url_for('resource.views.resourcehis_usage_list',
                yearmon=sel_yearmonth, kfzx=obj['id'], type=1),target='_blank'))

    def gs_num(value, obj):
        if obj['id']==u'总计':
            return str(Tag('a', value, href=url_for('resource.views.resourcehis_usage_list',
                yearmon=sel_yearmonth, type=2),target='_blank'))
        elif obj['id']==u'北测':
            return str(Tag('a', value, href=url_for('resource.views.resourcehis_usage_list',
                yearmon=sel_yearmonth, type=2, system=164),target='_blank'))
        else:
            return str(Tag('a', value, href=url_for('resource.views.resourcehis_usage_list',
                yearmon=sel_yearmonth, kfzx=obj['id'], type=2),target='_blank'))

    def total_num(value, obj):
        if obj['id']==u'总计':
            return str(Tag('a', value, href=url_for('resource.views.resourcehis_usage_list',
                yearmon=sel_yearmonth),target='_blank'))
        elif obj['id']==u'北测':
            return str(Tag('a', value, href=url_for('resource.views.resourcehis_usage_list',
                yearmon=sel_yearmonth, system=164),target='_blank'))
        else:
            return str(Tag('a', value, href=url_for('resource.views.resourcehis_usage_list',
                yearmon=sel_yearmonth, kfzx=obj['id']),target='_blank'))
    
    def zx_bg_rate(value, obj):
        if obj['id']==u'总计':
            return str(Tag('a', value, href=url_for('resource.views.resource_humans',
                workload = 1, kfzx = 99, flag = 1),target='_blank',
                title=str(obj['zx_bg'])+'/'+str(obj['zx_bg_total'])))
        elif obj['id']==u'北测':
            return str(Tag('a', value, href=url_for('resource.views.resource_humans',
                kfzx = 98,workload = 1, flag = 1),target='_blank',
                title=str(obj['zx_bg'])+'/'+str(obj['zx_bg_total'])))
        else:
            return str(Tag('a', value, href=url_for('resource.views.resource_humans',
                kfzx = obj['id'],workload = 1, flag = 1),target='_blank',
                title=str(obj['zx_bg'])+'/'+str(obj['zx_bg_total'])))
            
    fields_convert_map = {'zx_workload':zx_workload,'gs_workload':gs_workload,
        'zx_num':zx_num,'gs_num':gs_num,'total_num':total_num,'zx_bg_rate':zx_bg_rate}
    
    #展示        
    view =  SimpleListView(header, x, id='req_count_table',table_width=True,
        pagination=False, fields_convert_map=fields_convert_map)
    
    if 'download' in request.GET:
        fields_convert_map = {}
        return view.download('center_resource_month.xls', action='download', 
            timeout=0,domain=settings.PARA.DOMAIN)
    elif 'data' in request.values:
        return json(view.json())
    else:
        result = view.run(head=True, body=True)
        result['table'] = view
        result.update({'query_form':query.form})
        return result


#管理类报表-系统资源投入月报
@expose('/charts/manage/resource/sys/month')
def charts_manage_resource_sys_month():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from query import create_manage_resource_sys_month_query
    from uliweb.core.html import Tag
    from decimal import Decimal
    
    query = create_manage_resource_sys_month_query(url_for(charts_manage_resource_sys_month))
    c = query.run()
    
    sel_yearmonth = None
    if c.get('year') and c.get('month'):
        year_list = dict(settings.PARA.YEAR_LIST)
        month_list = dict(settings.PARA.MONTH_LIST)
        sel_yearmonth = int(year_list[int(c['year'])])*100+int(month_list[int(c['month'])])
    
    days = float(get_month_days(str(sel_yearmonth)))
    
    sys = get_model('system')
    zx = get_model('kfzx')
    task = get_model('tasks_details')
    resource = get_model('resourcehis')

    #生成表头
    header = []
    header.append({'name':'sys_id','verbose_name':u'系统编号', 'width':70})
    header.append({'name':'name','verbose_name':u'系统名称', 'width':200})
    header.append({'name':'type','verbose_name':u'系统属性', 'width':70})
    header.append({'name':'kfzx','verbose_name':u'所属中心', 'width':70})        
    header.append({'name':'zx_workload','verbose_name':u'中心人员资源投入情况/中心工作量|考勤时长', 'width':120})
    header.append({'name':'zx_dev_rate','verbose_name':u'中心人员资源投入情况/开发工作量占比', 'width':120})    
    header.append({'name':'zx_pc','verbose_name':u'中心人员资源投入情况/数据偏差率', 'width':80})    
    header.append({'name':'zx_num','verbose_name':u'中心人员资源投入情况/中心人数(含借调)', 'width':100})
    header.append({'name':'zx_qd','verbose_name':u'中心人员资源投入情况/中心人员工作强度', 'width':100})
#    header.append({'name':'zx_bg','verbose_name':u'中心人员资源投入情况/中心人员报工率', 'width':100})    
    header.append({'name':'gs_workload','verbose_name':u'公司人员资源投入情况/公司工作量|考勤时长', 'width':120})
    header.append({'name':'gs_dev_rate','verbose_name':u'公司人员资源投入情况/开发工作量占比', 'width':120})    
    header.append({'name':'gs_pc','verbose_name':u'公司人员资源投入情况/数据偏差率', 'width':80})    
    header.append({'name':'gs_num','verbose_name':u'公司人员资源投入情况/公司人数', 'width':80})
    header.append({'name':'gs_qd','verbose_name':u'公司人员资源投入情况/公司人员工作强度', 'width':100})
    header.append({'name':'total_workload','verbose_name':u'合计/工作量合计', 'width':80})
    header.append({'name':'total_num','verbose_name':u'合计/人数合计', 'width':70})
    
    condition = None
    if c.get('system'):
        condition = (sys.c.id == (int(c['system']))) & condition

    if c.get('zx'):
        condition = (sys.c.kfzx == (int(c['zx']))) & condition
        
    if request.user.department == '2':
        if request.user.is_gm:
            condition = (sys.c.kfzx== request.user._kfzx_)  & condition
        else:
            ids = request.user.users_systems.ids()
            condition = (sys.c.id.in_(ids)) & condition
    #取非虚拟系统
    condition = (sys.c.sys_type!='2') & condition
    #滤掉香港支持中心系统，20111111修改为显示
#    condition = (sys.c.kfzx != 8) & condition
    #初始化结构
    x = []
    if request.user.department != '3':    
        for k in sys.filter(condition).all().order_by(sys.c.sys_type,sys.c.kfzx):
            if k.sys_type == '5':
                continue                
            j = {}   
            j['name']=k.name
            j['type']=unicode(dict(settings.PARA.SYSTEM_TYPE)[k.sys_type],'utf-8')
            j['kfzx']=k.kfzx.name                               
            j['id']=k.id  
            j['sys_id']=k.sys_id
            j['zx_workload']=0
            j['zx_dev_rate']=0
            j['zx_pc']=0
            j['zx_num']=0
            j['zx_qd']=0
            j['gs_workload']=0
            j['gs_dev_rate']=0
            j['gs_pc']=0
            j['gs_num']=0
            j['gs_qd']=0
            j['total_workload']=0
            j['total_num']=0
            j['zx_worktime']=0
            j['gs_worktime']=0
            
            x.append(j)
    if request.user.department != '2' or request.user.department != '3':
        total={}
        total['id']=u'总计'
        total['sys_id']=u'总计'
        total['name']=u'--'
        total['type']=u'--'
        total['kfzx']=u'--'                
        total['zx_workload']=0
        total['zx_dev_rate']=0
        total['zx_pc']=0
        total['zx_num']=0
        total['zx_qd']=0
        total['gs_workload']=0
        total['gs_dev_rate']=0
        total['gs_pc']=0
        total['gs_num']=0
        total['gs_qd']=0
        total['total_workload']=0
        total['total_num']=0
        x.append(total)
    #生成数据

    condition_workload = (task.c.task_sys == sys.c.id) & condition
    condition_num = (resource.c.system == sys.c.id) & condition
        
    sql = select([task.c.task_sys,
            func.sum(task.c.amount_inner_input),
            func.sum(task.c.amount_outer_input),
            ],and_(task.c.year_month==sel_yearmonth,
            ~task.c.task_type.in_(['15','16','17','18']),condition_workload))\
            .group_by(task.c.task_sys)
    #开发工作量
    sql_dev = select([task.c.task_sys,
            func.sum(task.c.amount_inner_input),
            func.sum(task.c.amount_outer_input),
            ],and_(task.c.year_month==sel_yearmonth,
            task.c.task_type.in_(['01','02','03','04','05','06']),condition_workload))\
            .group_by(task.c.task_sys)
    
            #.order_by(func.sum(task.c.amount_inner_input)+func.sum(task.c.amount_outer_input))
    #投入资源情况，排除9090专业化测试系统，作为测试中心人员统计
    sql_res = select([resource.c.system,resource.c.type,
            func.sum(resource.c.workload*resource.c.workday/days),
            func.sum(resource.c.worktime/days),
            ],and_(resource.c.yearmon==sel_yearmonth,
            resource.c.system!=164,condition_num))\
            .group_by(resource.c.system,resource.c.type)
    
    if request.user.department != '3':
        sql_rs = sql.execute()
        for row in sql_rs:
            system,zx_sum,gs_sum = row
            for i in x:
                #20110111增加判断该任务所属系统是否为空判断，如果所属系统为空，则跳过该条统计记录
                if not system:
                    continue
                #print 'zx',zx
                if i['id']==system:
                    i['zx_workload']+=zx_sum
                    i['gs_workload']+=gs_sum
                    i['total_workload']+=gs_sum+zx_sum                                                
                if i['id']==u'总计':
                    i['zx_workload']+=zx_sum
                    i['gs_workload']+=gs_sum
                    i['total_workload']+=gs_sum+zx_sum                                                
        sql_dev_rs = sql_dev.execute()
        for row in sql_dev_rs:
            system,zx_sum,gs_sum = row
            for i in x:
                #20110111增加判断该任务所属系统是否为空判断，如果所属系统为空，则跳过该条统计记录
                if not system:
                    continue
                #print 'zx',zx
                if i['id']==system:
                    i['zx_dev_rate']+=zx_sum
                    i['gs_dev_rate']+=gs_sum
                if i['id']==u'总计':
                    i['zx_dev_rate']+=zx_sum
                    i['gs_dev_rate']+=gs_sum
                 
        sql_res_rs = sql_res.execute()
        for row in sql_res_rs:
            system,type,num,worktime = row
            for i in x:
                #20110111增加判断该任务所属系统是否为空判断，如果所属系统为空，则跳过该条统计记录
                if not system:
                    continue
                #中心人员包括中心人员和借调人员
                if i['id']==system and (type == '1' or type == '3'):
                    i['zx_num']+=num
                    i['zx_worktime']+=worktime
                    i['total_num']+=num
                if i['id']==system and type == '2':
                    i['gs_num']+=num
                    i['gs_worktime']+=worktime
                    i['total_num']+=num
                
                if i['id']==u'总计' and (type == '1' or type == '3'):
                    i['zx_num']+=num
                    i['total_num']+=num                                               
                if i['id']==u'总计' and type == '2':
                    i['gs_num']+=num
                    i['total_num']+=num                                                
                
        
    
    for i in x:
        if i['zx_workload']>0:
            i['zx_dev_rate'] = str(round(float(i['zx_dev_rate'])/float(i['zx_workload']),4)*100)+'%'
        if i['gs_workload']>0:
            i['gs_dev_rate'] = str(round(float(i['gs_dev_rate'])/float(i['gs_workload']),4)*100)+'%'

        if i['zx_num']>0:
            i['zx_qd'] = i['zx_workload']/days/float(i['zx_num'])
        if i['gs_num']>0:
            i['gs_qd'] = i['gs_workload']/days/float(i['gs_num'])
        
        if (i['id']!=u'总计') and (i['zx_worktime']>0) and (i['zx_workload']/days/float(i['zx_worktime'])>1):
            i['zx_pc'] = round(i['zx_workload']/days/float(i['zx_worktime'])-1,3)*100
        elif (i['id']!=u'总计') and (i['zx_worktime']==0) and (i['zx_workload']>0):
            i['zx_pc']=u'不符'

        if (i['id']!=u'总计') and (i['gs_worktime']>0) and (i['gs_workload']/days/float(i['gs_worktime'])>1):
            i['gs_pc'] = round(i['gs_workload']/days/float(i['gs_worktime'])-1,3)*100
        elif (i['id']!=u'总计') and (i['gs_worktime']==0) and (i['gs_workload']>0):
            i['gs_pc']=u'不符'        
                
        i['zx_workload']=str(round(i['zx_workload']/days,1))
        i['gs_workload']=str(round(i['gs_workload']/days,1))
        i['zx_num']=str(round(i['zx_num'],1))
        i['gs_num']=str(round(i['gs_num'],1))
        i['zx_qd']=str(round(i['zx_qd'],1))
        i['gs_qd']=str(round(i['gs_qd'],1))
        i['total_num']=str(round(i['total_num'],1))        
        i['total_workload']=str(round(i['total_workload']/days,1))
        
    def zx_workload(value, obj):
        if obj['id']==u'总计':
            return value
        else:
            return str(value)+"/"+str(round(obj['zx_worktime'],1))
    
    def zx_pc(value,obj):
        if obj['id']==u'总计':
            return value
        elif (obj['zx_pc']==u'不符'):
            return "<font color='red'>"+'不符'+"</font>"
        elif (obj['zx_pc']>15):
            return "<font color='red'>"+str(obj['zx_pc'])+'%'+"</font>"
        elif (obj['zx_pc']<=15) and (obj['zx_pc']>0):
            return str(obj['zx_pc'])+'%'
        else:
            return ''
    def gs_pc(value,obj):
        if obj['id']==u'总计':
            return value
        elif (obj['gs_pc']==u'不符'):
            return "<font color='red'>"+'不符'+"</font>"
        elif (obj['gs_pc']>15):
            return "<font color='red'>"+str(obj['gs_pc'])+'%'+"</font>"
        elif (obj['gs_pc']<=15) and (obj['gs_pc']>0):
            return str(obj['gs_pc'])+'%'
        else:
            return ''
    
    def gs_workload(value, obj):
        if obj['id']==u'总计':
            return value
        else:
            return str(value)+"/"+str(round(obj['gs_worktime'],1))

    def zx_num(value, obj):
        if obj['id']==u'总计':
            return str(Tag('a', value, href=url_for('resource.views.resourcehis_usage_list',
                yearmon=sel_yearmonth, type=1),target='_blank'))
        else:
            return str(Tag('a', value, href=url_for('resource.views.resourcehis_usage_list',
                yearmon=sel_yearmonth, system=obj['id'], type=1),target='_blank'))

    def gs_num(value, obj):
        if obj['id']==u'总计':
            return str(Tag('a', value, href=url_for('resource.views.resourcehis_usage_list',
                yearmon=sel_yearmonth, type=2),target='_blank'))
        else:
            return str(Tag('a', value, href=url_for('resource.views.resourcehis_usage_list',
                yearmon=sel_yearmonth, system=obj['id'], type=2),target='_blank'))

    def total_num(value, obj):
        if obj['id']==u'总计':
            return str(Tag('a', value, href=url_for('resource.views.resourcehis_usage_list',
                yearmon=sel_yearmonth),target='_blank'))
        else:
            return str(Tag('a', value, href=url_for('resource.views.resourcehis_usage_list',
                yearmon=sel_yearmonth, system=obj['id']),target='_blank'))
    
    fields_convert_map = {'zx_workload':zx_workload,'gs_workload':gs_workload,
        'zx_num':zx_num,'gs_num':gs_num,'total_num':total_num,'zx_pc':zx_pc,'gs_pc':gs_pc}

    pageno = int(request.values.get('page', 1)) - 1
    rows_per_page = int(request.values.get('rows', settings.get_var('PARA/ROWS_PER_PAGE', 10)))
    
    #展示        
    view =  SimpleListView(header, x, id='req_count_table',table_width=True,
        pagination=True, fields_convert_map=fields_convert_map, pageno=pageno,
        rows_per_page=rows_per_page)
    
    if 'download' in request.GET:
        fields_convert_map = {}
        return view.download('resource_sys_month.xls', action='download', 
            timeout=0,fields_convert_map=fields_convert_map)
    elif 'data' in request.values:
        return json(view.json())
    else:
        result = view.run(head=True, body=True)
        result['table'] = view
        result.update({'query_form':query.form})
        return result

#@expose('charts/resource/time/requirement')
#def charts_resource_time_requirement():
#    from uliweb.orm import get_model
#    from uliweb.utils.generic import SimpleListView
#    from uliweb import request
#    
#    begin_Date,end_Date = get_date()
#    begin_month = begin_Date.month
#    end_month = end_Date.month
#
#    task = get_model('tasks_details')
#    sql = select([task.c.stage,
#                func.sum(task.c.amount_inner_input),
#                func.sum(task.c.amount_outer_input),
#                func.sum(task.c.amount_inner_design),
#                func.sum(task.c.amount_outer_design),
#                func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
#                func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),],
#                and_(task.c.year>=begin_Date.year,
#                task.c.year<=end_Date.year,task.c.month>=begin_month,
#                task.c.task_type.in_(['01','02','03','04']),
#                task.c.month<=end_month))\
#                .group_by(task.c.stage)\
#                .order_by(task.c.stage)
#    sql_rs = sql.execute()
#    
#    sql_ky = select([task.c.task_type,
#                func.sum(task.c.amount_inner_input),
#                func.sum(task.c.amount_outer_input),
#                func.sum(task.c.amount_inner_design),
#                func.sum(task.c.amount_outer_design),
#                func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
#                func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),],
#                and_(task.c.year>=begin_Date.year,
#                task.c.year<=end_Date.year,task.c.month>=begin_month,
#                task.c.task_type=='05',
#                task.c.month<=end_month))\
#                .group_by(task.c.task_type)
#    sql_ky_rs = sql_ky.execute()
#    
#    #生成数据
#    j = [0]*len(settings.PARA.resource_stage_date)
#    j.insert(0,u'总计')
#    x = []
#    map_stage = dict(settings.PARA.TASK_STAGE)
#    for row in sql_rs:
#        name,zx_sum,gs_sum,zx_sum_design,gs_sum_design,zx_sum_extra,gs_sum_extra = row
#        row = list(row)
#        row[0]= map_stage[name]
#        row.insert(1,zx_sum+gs_sum)
#        x.append(row)
#        for i in range(1,len(row)):
#            j[i]+=row[i]
#    #插入可研阶段数据
#    for row in sql_ky_rs:
#        row = list(row)
#        row[0]='可研'
#        row.insert(1,zx_sum+gs_sum)
#        x.insert(0,row)
#        for i in range(1,len(row)):
#            j[i]+=row[i]
#    #插入汇总数据
#    x.append(j)
#    
#    view =  SimpleListView(settings.PARA.resource_stage_date,x,id='req_count_table')
#                
#    
#    if 'download' in request.GET:
#        return view.download('charts_resource_stage_date_'+str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
#    else:
#        result = view.run()
#        result['begin_date'] = begin_Date
#        result['end_date'] = end_Date
#        return result
    
#系统自主研发率报表
@expose('/charts/resource/independent/sys/date')
def charts_resource_independent_sys_date():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    month_list = get_month_list(begin_Date,end_Date)
    kfzx = get_model('kfzx')
    sys = get_model('system')
    selfdev = get_model('selfdev')
    resource = get_model('resource')
    #查询系统对应的资源数
    sql = select([sys.c.kfzx,sys.c.sys_id,sys.c.name,
                selfdev.c.qualified,selfdev.c.complexity,
                func.sum(resource.c.inner_number),
                func.sum(resource.c.outter_number),
                selfdev.c.independent_lastyear,
                selfdev.c.independent_thisyear,
                selfdev.c.independent_destination,
                selfdev.c.system],
                and_(resource.c.year_month>=begin,
                resource.c.year_month<=end,sys.c.id==resource.c.system,
                sys.c.id==selfdev.c.system),)\
                .group_by(sys.c.sys_id)\
                .order_by(sys.c.sys_id)
    sql_rs = sql.execute()
    
    #查询任务书数量
    ass = get_model('assignment')
    ass_sys = ass.systems.table
    ass = select([ass_sys.c.system_id,ass.c.type,func.count(ass.c.id)],
        or_(and_(ass.c.release_date<begin_Date,
        ass.c.end_date>begin_Date,
        ass.c.id==ass_sys.c.assignment_id),
        and_(ass.c.release_date>=begin_Date,
        ass.c.release_date<=end_Date,
        ass.c.id==ass_sys.c.assignment_id)))\
        .group_by(ass_sys.c.system_id,ass.c.type)    
    ass_rs = ass.execute()
    #生成数据
    x = []
    i = [0]*23
    for row in sql_rs:
        zx,sys_abbr,sys_name,qualified,complexity,inner_number,\
        outter_number,lastyear,thisyear,destination,sys_id = row
        row = list(row)
        #中心
        i[0] = kfzx.get(zx).name
        #系统编号
        i[1] = sys_abbr
        #系统名称
        i[2] = sys_name
        #是否当年达标系统
        i[3] = qualified
        #复杂度
        i[4] = complexity
        for ass_row in ass_rs:            
            sys,type,num=ass_row
            if (sys==sys_id)and(int(type) in [1,2]):
                #项目数量
                i[5]=num
            if (sys==sys_id)and(int(type) == 3):    
                #非项目数量
                i[6]=num
        #总投入
        i[7]=inner_number+outter_number
        #中心投入
        i[8]=inner_number
        #公司投入
        i[9]=outter_number
        #工作量级别
        i[10]=get_workload_level((inner_number+outter_number)/len(month_list))
        #自主率
        if inner_number+outter_number>0:            
            i[11] = round(float(inner_number)*100/(inner_number+outter_number),2)
        else:
            i[11] = 0
        #研发类型
        i[12] = get_develop_type(i[11])
        #和上年底比较/目标自主率
        i[13] = lastyear
        #和上年底比较/目标研发类型
        i[14] = get_develop_type(int(lastyear))
        #和上年底比较/自主率差值
        i[15] = i[11]-int(lastyear)
        #和上年底比较/类型是否达标
        if (i[15]<0)and(i[14]!=i[12]):
            i[16]='否'
        else:
            i[16]='是'
        #和本年底比较/目标研发类型
        i[17] = thisyear
        #和上年底比较/目标研发类型
        i[18] = get_develop_type(int(thisyear))
        #和上年底比较/自主率差值
        i[19] = i[11]-int(thisyear)
        #和上年底比较/类型是否达标
        if (i[19]<0)and(i[18]!=i[12]):
            i[20]='否'
        else:
            i[20]='是'
        #和最终目标比较/目标研发类型
        i[21] = destination
        #和最终目标比较/类型是否达标
        if ((i[11]-int(destination))<0)and(i[21]!=i[12]):
            i[22]='否'
        else:
            i[22]='是'
        x.append(i)

    view =  SimpleListView(settings.PARA.resource_independent_sys_date,
        x,id='req_count_table',pagination=False)                
    
    if 'download' in request.GET:
        return view.download('charts_resource_independent_sys_date'+
            str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#生成工作量级别
def get_workload_level(number):
    from uliweb import settings

    low = settings.PARA.workload_level[0]
    common = settings.PARA.workload_level[1]
    high = settings.PARA.workload_level[2]
    if number<low[0]:
        return low[1]
    else:
        if number<common[0]:
            return common[1]
        else:
            return high[1]

#生成研发类型       
def get_develop_type(number):
    from uliweb import settings

    type_4 = settings.PARA.develop_type[0]
    type_3c = settings.PARA.develop_type[1]
    type_3b = settings.PARA.develop_type[2]
    type_3a = settings.PARA.develop_type[3]
    type_2b = settings.PARA.develop_type[4]
    type_2a = settings.PARA.develop_type[5]
    type_1 = settings.PARA.develop_type[6]
    
    if number<type_4[0]:
        return type_4[1]
    elif number<type_3c[0]:
        return type_3c[1]
    elif number<type_3b[0]:
        return type_3b[1]
    elif number<type_3a[0]:
        return type_3a[1]
    elif number<type_2b[0]:
        return type_2b[1]
    elif number<type_2a[0]:
        return type_2a[1]
    else:
        return type_1[1]
    
#中心自主研发率报表
@expose('/charts/resource/independent/zx/date')
def charts_resource_independent_zx_date():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    kfzx = get_model('kfzx')
    selfdev_zx = get_model('selfdev_zx')
    resource = get_model('resource')
    #查询系统对应的资源数
    sql = select([resource.c.system,resource.c.kfzx,kfzx.c.name,
                func.sum(resource.c.inner_number),
                func.sum(resource.c.outter_number)],
                and_(resource.c.year_month>=begin,
                resource.c.year_month<=end,
                resource.c.kfzx==kfzx.c.id),)\
                .group_by(resource.c.system)\
                .order_by(resource.c.system)
    sql_rs = sql.execute()
    
    #初始化中心自主率
    y = []
    for row in selfdev_zx.all().order_by(selfdev_zx.c.kfzx):
        j = [0]*11
        j[0] = row.kfzx.name
        j[1] = row.initial
        j[2] = row.lastyear
        j[3] = row.thisyear
        j[4] = row.destination
        y.append(j)
    #生成数据
    for row in sql_rs:
        sys,zx,zx_name,inner_number,outter_number = row
        #计算自主率
        if inner_number+outter_number>0:            
            x = round(float(inner_number)*100/(inner_number+outter_number),2)
        else:
            x = 0
        
        for i in y:
            if i[0]==zx_name:
                i[9]+=inner_number
                i[10]+=outter_number
                #自主率求和
                i[7]+=x
                #系统个数
                i[8]+=1
                #平均自主率
                if i[8]>0:
                    i[5]=round(float(i[7])/i[8],2)
                #总体自主率
                if i[9]+i[10]>0:
                    i[6]=round(float(i[9])*100/(i[9]+i[10]),2)
            
    view =  SimpleListView(settings.PARA.resource_independent_zx_date,
        y,id='req_count_table',pagination=False)                
    
    if 'download' in request.GET:
        return view.download('charts_resource_independent_sys_date'+
            str(begin_Date)+'_'+str(end_Date)+'.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#延迟版本列表展示
@expose('/charts/plan/version/delay/list')
def charts_plan_version_delay_list():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from uliweb.utils.common import get_var
    
    tasks = get_model('tasks_develop')
    new = select([tasks.c.name,tasks.c.stage,tasks.c.begin_time,tasks.c.end_time,tasks.c.status,tasks.c.delay_classes,tasks.c.delay_reason,tasks.c.task_id],
                 and_(tasks.c.status.in_(['2','4','5'])))
    new_rs = new.execute()
    x=[]     
    for row in new_rs:
        name, stage, begin_date,end_date,status,delay_classes,delay_reason,task_id = row
        #type = settings.PARA.assignment_new_map[type]  
        row = list(row)
        row[1]=dict(settings.PARA.TASK_STAGE)[stage]
        row[4]=dict(settings.PARA.TASK_STATUS)[status]
        row[5]=dict(settings.PARA.TASK_DELAY_CLASSES)[delay_classes]
        x.append(row)
            
    view =  SimpleListView(settings.PARA.version_plan_delay, x,
        pagination=False,id='req_count_table')
        
    result = view.run()
    return result

#get_plan_version_change_rate为获取计划变更率函数
def get_plan_version_change_rate(low,high):
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from uliweb.utils.common import get_var
    
    req_det = get_model('milestone_details')
    req_task = get_model('tasks_develop')
    req_sys = get_model('system')
    req_kfzx = get_model('kfzx')
    #new语句以任务编号为基础统计该任务计划更改次数，一天更改多次算一次更改
    user_zx = request.user._kfzx_
    if request.user.department=='2':
        new = select([req_sys.c.name,req_task.c.name,func.count(func.distinct(func.substr(req_det.c.modified_date,1,10))),req_kfzx.c.name],
                     and_(req_det.c.task == req_task.c.id,
                          ~req_task.c.status.in_(['3','4','5']),
                          req_task.c.task_sys == req_sys.c.id,
                          req_task.c.kfzx == req_kfzx.c.id,
                          req_task.c.kfzx == user_zx)
                       ).group_by(req_det.c.task).having(and_(func.count(func.distinct(func.substr(req_det.c.modified_date,1,10))) >= low,func.count(func.distinct(func.substr(req_det.c.modified_date,0,11))) < high))
    else:            
        new = select([req_sys.c.name,req_task.c.name,func.count(func.distinct(func.substr(req_det.c.modified_date,1,10))),req_kfzx.c.name],
                     and_(req_det.c.task == req_task.c.id,
                          ~req_task.c.status.in_(['3','4','5']),
                          req_task.c.task_sys == req_sys.c.id,
                          req_task.c.kfzx == req_kfzx.c.id)
                    ).group_by(req_det.c.task).having(and_(func.count(func.distinct(func.substr(req_det.c.modified_date,1,10))) >= low,func.count(func.distinct(func.substr(req_det.c.modified_date,0,11))) < high))
    new_rs = new.execute()
    data=[]     
    for row in new_rs:
        sys_name, task_name, plan_alter,kfzx_name = row
        #type = settings.PARA.assignment_new_map[type]  
        row = list(row)
        #row[1]=dict(settings.PARA.TASK_STAGE)[stage]
        #row[4]=dict(settings.PARA.TASK_STATUS)[status]
        #row[5]=dict(settings.PARA.TASK_DELAY_CLASSES)[delay_classes]
        data.append(row)
    return data

#版本版本变更率统计--提示级
@expose('/charts/plan/version/change/suggestive')
def charts_plan_version_change_suggestive():
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from uliweb.utils.common import get_var
    
    low = 2
    high = 5
    x = get_plan_version_change_rate(low,high)
    view =  SimpleListView(settings.PARA.version_change_suggestive, x,
        pagination=False,id='charts_plan_version_change_suggestive')
        
#    if 'data' in request.GET:
#        result = view.run(head=False, body=True)
#        return json(result)
#    else:
#        result = view.run(head=True, body=False)
#    result = view.run()
#    return result
    if 'download' in request.GET:
      download_name = 'charts_plan_version_change_suggestive' + '.xls'
      return view.download(download_name, action='download', timeout=10)               
    else:
      result = view.run()
#      result['begin_date'] = begin_Date
#      result['end_date'] = end_Date
    return result
    

#版本版本变更率统计--通报级
@expose('/charts/plan/version/change/notice')
def charts_plan_version_change_notice():
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from uliweb.utils.common import get_var
    
    low = 5
    high = 6
    x = get_plan_version_change_rate(low,high)
    view =  SimpleListView(settings.PARA.version_change_suggestive, x,
        pagination=False,id='charts_plan_version_change_notice')
        
    if 'download' in request.GET:
      download_name = 'charts_plan_version_change_notice' + '.xls'
      return view.download(download_name, action='download', timeout=10)               
    else:
      result = view.run()
#      result['begin_date'] = begin_Date
#      result['end_date'] = end_Date
    return result

#版本版本变更率统计--介入级
@expose('/charts/plan/version/change/involved')
def charts_plan_version_change_involved():
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from uliweb.utils.common import get_var
    
    low = 6
    high = 9999
    x = get_plan_version_change_rate(low,high)
            
    view =  SimpleListView(settings.PARA.version_change_suggestive, x,
        pagination=False,id='charts_plan_version_change_involved')
        
    if 'download' in request.GET:
      download_name = 'charts_plan_version_change_involved' + '.xls'
      return view.download(download_name, action='download', timeout=10)               
    else:
      result = view.run()
#      result['begin_date'] = begin_Date
#      result['end_date'] = end_Date
    return result

def get_list_view(c):
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from uliweb.utils.common import get_var
    from uliweb.core.html import Tag
    from requirements.views import req_view
    from uliweb import settings
    from uliweb.utils.common import get_choice

    pageno = int(request.GET.get('pageno', 0))
    
    Req_report = get_model('manage_requirement_week')
    Req = get_model('requirement')
    tx = get_model('tx')
    zx = get_model('kfzx')
    sys = get_model('system')
    
    query = None
    condition = None
    condition = (Req_report.c.req_id == Req.c.id) & condition
    #condition = (Req.c.main_sys == sys.c.id) & condition
    #修改后条线可以看到其他条线的信息，20110629
    if not c.get('tx'):
        if request.user.department == '1':
            condition = (Req.c.tx == request.user._tx_) & condition
    if request.user.department == '2':
        if request.user.is_gm:
            condition = Req.kfzxs.in_(request.user._kfzx_) & condition
        else:
            ids = request.user.users_systems.ids()
            condition = Req.systems.in_(*ids) & condition
    
    if c.get('report_type'):
        condition = (Req_report.c.report_type == int(c['report_type'])) & condition
    if c.get('req_id'):
        condition = (Req.c.req_id.like('%'+c['req_id']+'%')) & condition
    if c.get('tx'):
        condition = (Req.c.tx == int(c['tx'])) & condition
    if c.get('kfzx'):
        condition = (Req.kfzxs.in_(int(c['kfzx']))) & condition
    if c.get('main_zx'):
        condition = (Req.c.main_sys==sys.c.id) & condition
        condition = (sys.c.kfzx==int(c['main_zx'])) & condition
    if c.get('system'):
        condition = (Req.systems.in_(int(c['system']))) & condition
    if c.get('main_sys'):
        condition = (Req.c.main_sys == int(c['main_sys'])) & condition    
    if c.get('title'):
        condition = (Req.c.title.like('%'+unicode(c['title'],'utf-8')+'%')) & condition
    if c.get('t_nproject'):
        condition = (Req_report.c.t_nproject==c['t_nproject']) & condition
    if c.get('t_project'):
        condition = (Req_report.c.t_project==c['t_project']) & condition
    if c.get('t_nassignment'):
        condition = (Req_report.c.t_nassignment==c['t_nassignment']) & condition
    if c.get('t_undetermined'):
        condition = (Req_report.c.t_undetermined==c['t_undetermined']) & condition
    if c.get('t_delay_flag'):
        condition = (Req_report.c.t_delay_flag==c['t_delay_flag']) & condition
        
    
    #取最近的报表日期
    last_date = ''
    sql_date = select([func.max(Req_report.c.report_date)],condition)
    sql_date_rs = sql_date.execute()
    for row in sql_date_rs:
        last_date = row[0]

    if c.get('begin_date'):
        condition = (Req_report.c.report_date >= c['begin_date']) & condition
    else:
        condition = (Req_report.c.report_date == last_date) & condition
    if c.get('end_date'):
        condition = (Req_report.c.report_date <= c['end_date']) & condition
    if c.get('report_date'):
        condition = (Req_report.c.report_date == c['report_date']) & condition

    pageno = int(request.values.get('page', 1)) - 1
    rows_per_page = int(request.values.get('rows', 10))
    
    sql = select([Req.c.req_id,Req.c.title,Req.c.mode,Req.c.stage,
                 Req_report.c.version_amount,
                 Req_report.c.task_amount,
                 Req_report.c.finish_stat,
                 Req.c.tx,Req.c.main_sys,
                 Req_report.c.delay_flag,
                 Req_report.c.delay_stat,
                 Req_report.c.delay_reason,
                 Req_report.c.plan_stat,
                 Req_report.c.inner_resource_num,
                 Req_report.c.inner_resource_input,
                 Req_report.c.outer_resource_num,
                 Req_report.c.outer_resource_input,                 
                 Req_report.c.new_task_amount,
                 Req_report.c.report_date,Req.c.id],
                 condition).order_by(Req.c.req_id)#.offset(rows_per_page*pageno).limit(rows_per_page)
    sql_rs = sql.execute()
    data=[]
    i = []     
    for row in sql_rs:
        row = list(row)
        row[0] = str(Tag('a', row[0], href=url_for(req_view, id=row[19])))
        row[3] = unicode(get_choice(settings.get_var('PARA/REQ_STAGE').get(row[2], []), row[3]), 'utf8')
        row[2] = unicode(dict(settings.PARA.REQ_MODE)[row[2]],'utf8')
        row[7] = tx.get(row[7]).name
        sys_obj = sys.get(row[8])
        row.append(sys_obj.abbr)
#        row[8] = zx.get(sys.get(row[8])._kfzx_).name
        row[8] = sys_obj.kfzx.name
        #增加系统列表
        sys_list = list(Req.get(row[19]).systems)
        i = ''
        for x in sys_list:
            i+=x.abbr+' '        
        row.insert(9,i)
        
        row[13] = str((round(row[13],4))*100)+'%'
        row[14] = round(row[14],2)
        row[15] = str((round(row[15],4))*100)+'%'
        row[16] = round(row[16],2)
        row[17] = str((round(row[17],4))*100)+'%'

        if row[6]:
            row[6]=u'是'
        else:
            row[6]=u'否'
        if row[10]:
            row[10]=u'是'
        else:
            row[10]=u'否'
            
        data.append(row)

    header = []
    header.append({'name':'id','verbose_name':'准开发需求编号', 'width':100})
    header.append({'name':'title','verbose_name':'准开发需求名称', 'width':100})
    header.append({'name':'type','verbose_name':'实施方式', 'width':70})        
    header.append({'name':'stage','verbose_name':'所处阶段', 'width':70})    
    header.append({'name':'version_amount','verbose_name':'共需投产次数', 'width':90})
    header.append({'name':'task_amount','verbose_name':'工作任务数量', 'width':90})
    header.append({'name':'finish_stat','verbose_name':'全部任务是否完成', 'width':100})
    header.append({'name':'tx','verbose_name':'所属条线', 'width':70})
    header.append({'name':'kfzx','verbose_name':'牵头中心', 'width':70})
    header.append({'name':'systems','verbose_name':'相关系统', 'width':100})
    header.append({'name':'delay_if','verbose_name':'是否延迟', 'width':60})
    header.append({'name':'delay_stat','verbose_name':'已延迟里程碑', 'width':600})
    header.append({'name':'delay_reason','verbose_name':'延迟原因', 'width':70})
    header.append({'name':'plan_stat','verbose_name':'计划进度完成', 'width':80})
    header.append({'name':'inner_resource_num','verbose_name':'中心实际投入资源(人月)', 'width':135})    
    header.append({'name':'inner_input','verbose_name':'中心实际投入资源较计划', 'width':150})
    header.append({'name':'outer_resource_num','verbose_name':'公司实际投入资源(人月)', 'width':135})    
    header.append({'name':'outer_input','verbose_name':'公司实际投入资源较计划', 'width':150})
    header.append({'name':'new_task_amount','verbose_name':'新增任务数量', 'width':90})
    header.append({'name':'report_date','verbose_name':'报表日期', 'width':70})
    
    view =  SimpleListView(header, data, rows_per_page=rows_per_page, pageno=pageno)
    return view

#管理类报表----准开发需求跟踪
@expose('/charts/manage/requirements')
def charts_manage_requirements():
    from query import create_manage_requirement_query
    
    query = create_manage_requirement_query(url_for(charts_manage_requirements))
    condition = query.run()
    view = get_list_view(condition)
    if 'download' in request.GET:
#        def id(value, obj):
#            i = value.find('>')
#            j = value.rfind('<')
#            k = value[i+1:j]
#            return str(k)
##
#        fields_convert_map = {'id':id}
        return view.download('requirements.xls', action='download', 
            timeout=0)
#            timeout=0,fields_convert_map=fields_convert_map)
    elif 'data' in request.GET:
        return json(view.json())
#        result = view.run(head=False, body=True)
#        return json(result)
    else:
        result = view.run(head=True, body=False)
        result['table'] = view
        if request.GET.get('flag'):
            result.update({'query_form':''})
        else:
            result.update({'query_form':query.form})
        return result


def boolean_convert(b, obj):
    if b:
        return '<div class="ui-icon ui-icon-check"></div>'
    else:
        return '<div class="ui-icon ui-icon-closethick"></div>'

#管理类报表----任务书列表
@expose('/charts/manage/assignments')
def charts_manage_assignments():
    from query import create_manage_assignments_query
    from uliweb.utils.generic import ListView
    from uliweb import orm
    from uliweb.core.html import Tag
    from uliweb.utils.common import get_choice
    import generic.views as g_views
    
    query_view = create_manage_assignments_query(url_for(charts_manage_assignments))
    c = query_view.run()

    ass_report = get_model('assignment_report_detail')
    kfzx_mod = get_model('kfzx')
    tx_mod = get_model('tx')
    query = None
    condition = None

#    if request.user.department == '1':
#        condition = (ass_report.c.tx == request.user._tx_) & condition
#    elif request.user.department == '2':
    if request.user.department == '2':
        if request.user.is_gm:
            condition = (ass_report.c.kfzx== request.user._kfzx_)  & condition
        else:
            ids = request.user.users_systems.ids()
            condition = (ass_report.c.system.in_(ids)) & condition
    
    if c.get('tx'):
        condition = (ass_report.c.tx == (int(c['tx']))) & condition
    if c.get('ass_id'):
        condition = (ass_report.c.ass_id.like('%'+c['ass_id']+'%')) & condition
    if c.get('kfzx'):
        condition = (ass_report.c.kfzx == int(c['kfzx'])) & condition
    if c.get('status'):
        condition = (ass_report.c.status == int(c['status'])) & condition
    if c.get('requirement'):
        condition = (ass_report.requirements.in_(int(c['requirement']))) & condition
    if c.get('system'):
        condition = (ass_report.c.system == (int(c['system']))) & condition
    if c.get('type'):
        condition = (ass_report.c.type == c['type']) & condition
    if c.get('stage'):
        condition = (ass_report.c.stage == c['stage']) & condition
    if c.get('t_implement'):
        condition = (ass_report.c.t_implement==c['t_implement']) & condition
    if c.get('t_finish'):
        condition = (ass_report.c.t_finish==c['t_finish']) & condition
    if c.get('t_js'):
        condition = (ass_report.c.t_js==c['t_js']) & condition    
    if c.get('t_ys'):
        condition = (ass_report.c.t_ys==c['t_ys']) & condition
    if c.get('t_delay'):
        condition = (ass_report.c.t_delay==c['t_delay']) & condition
    if c.get('t_task_delay'):
        condition = (ass_report.c.t_task_delay==c['t_task_delay']) & condition
    if c.get('t_status'):
        condition = (ass_report.c.t_status==c['t_status']) & condition
    if c.get('report_date'):
        condition = (ass_report.c.report_date == c['report_date']) & condition
    if c.get('report_type'):
        condition = (ass_report.c.report_type == c['report_type']) & condition
    
    def ass_id(value, obj):
        return str(Tag('a', value.split('_',1)[0], href=url_for('assignments.views.assignments_view', id=obj.ass)))
        
    def stage(value, obj):
        return get_choice(settings.PARA.ASSIGNMENT_STAGE[obj.type], value)

    def kfzx(value, obj):
        return kfzx_mod.get(value).name
    
    def tx(value, obj):
        return tx_mod.get(value).name
    
    def t_task_delay(value,obj):
        if value==1:
            return u"是"
        else:
            return u"否" 

    def t_delay(value,obj):
        if value==1:
            return u"是"
        else:
            return u"否" 
    
    pageno = int(request.values.get('page', 1)) - 1
    rows_per_page = int(request.values.get('rows', settings.get_var('PARA/ROWS_PER_PAGE', 10)))
    
    fields_convert_map = {'ass_id':ass_id, 'stage':stage, 'risk_if':boolean_convert, 
        'advanced_if':boolean_convert,'kfzx':kfzx,'tx':tx,
        't_task_delay':t_task_delay,'t_delay':t_delay}
    
    model = 'assignment_report_detail'
    _id = '%s_table' % model
    view =  ListView(model, condition=condition, rows_per_page=rows_per_page, 
        pageno=pageno, id=_id, fields_convert_map=fields_convert_map)
        
    if 'download' in request.GET:
        def stage(value, obj):
            return unicode(get_choice(settings.PARA.ASSIGNMENT_STAGE[obj.type], value), 'utf8')
        fields_convert_map = {'stage':stage}
        return view.download('assignments.xls', action='download', timeout=0,
            fields_convert_map=fields_convert_map)
    else:
        data = {'query_form':query_view.form}
        
        return g_views.generic_list(model, template='charts_manage_assignments.html',
            view=view, data=data, json_result=True)


#管理类报表----任务列表
@expose('/charts/manage/tasks/develop')
def charts_manage_tasks_develop():
    from query import create_manage_tasks_develop_query
    from uliweb.utils.generic import ListView
    from uliweb import orm
    from uliweb.core.html import Tag
    from uliweb.utils.common import get_choice
    import generic.views as g_views
    
    query_view = create_manage_tasks_develop_query(url_for(charts_manage_tasks_develop))
    c = query_view.run()

    tasks_report = get_model('tasks_develop_report_detail')
    tasks = get_model('tasks_develop')
    sys = get_model('system')
    req = get_model('requirement')
    tx_mod = get_model('tx')
    
    query = None
    condition = None
    
#    if request.user.department == '1':
#        condition = (tasks_report.c.tx == request.user._tx_) & condition
#    elif request.user.department == '2':
    if request.user.department == '2':
        if request.user.is_gm:
            condition = (tasks_report.c.kfzx== request.user._kfzx_)  & condition
        else:
            ids = request.user.users_systems.ids()
            condition = (tasks_report.c.task_sys.in_(ids)) & condition
    
    
    if c.get('task_id'):
        condition = (tasks_report.c.task_id.like('%'+c['task_id']+'%')) & condition
    if c.get('requirement'):
        condition = (tasks_report.c.requirement == int(c['requirement'])) & condition
    if c.get('kfzx'):
        condition = (tasks_report.c.kfzx == int(c['kfzx'])) & condition
    if c.get('task_sys'):
        condition = (tasks_report.c.task_sys == int(c['task_sys'])) & condition
    if c.get('status'):
        condition = (tasks_report.c.status == c['status']) & condition
    if c.get('name'):
        condition = (tasks_report.c.name.like('%'+c['name']+'%')) & condition
    if c.get('task_type'):
        condition = (tasks_report.c.task_type == c['task_type']) & condition
    if c.get('t_implement'):
        condition = (tasks_report.c.t_implement==c['t_implement']) & condition
    if c.get('t_finish'):
        condition = (tasks_report.c.t_finish==c['t_finish']) & condition
    if c.get('t_version'):
        condition = (tasks_report.c.t_version==c['t_version']) & condition
    if c.get('t_delay'):
        condition = (tasks_report.c.t_delay==c['t_delay']) & condition
    if c.get('tx'):
        condition = (tasks_report.c.tx==c['tx']) & condition
    if c.get('report_date'):
        condition = (tasks_report.c.report_date == c['report_date']) & condition
    if c.get('report_type'):
        condition = (tasks_report.c.report_type == c['report_type']) & condition
    if c.get('milestone_delay_num'):
        condition = (tasks_report.c.milestone_delay_num>0) & condition
    if c.get('milestone_change_num'):
        condition = (tasks_report.c.milestone_change_num>0) & condition
    if c.get('milestone_total_num'):
        condition = (tasks_report.c.milestone_total_num>0) & condition

    if c.get('begin_date') and c.get('end_date'):
        condition = (tasks_report.c.report_date >= c['begin_date']) & condition
        condition = (tasks_report.c.report_date <= c['end_date']) & condition

    def task_id(value, obj):
        return str(Tag('a', value, href=url_for('tasks.views.tasks_develop_view',task_type='develop', id=obj.task)))
        
    def tx(value, obj):
        return tx_mod.get(value).name
    
    def milestone_delay_rate(value, obj):
        return str(round(value,2))

    def milestone_change_rate(value, obj):
        return str(round(value,2))

    def t_delay(value,obj):
        if value==1:
            return u"是"
        else:
            return u"否" 
    
    
#    def stage(value, obj):
#        return get_choice(settings.PARA.TASK_STAGE[obj.type], value)
    
    pageno = int(request.values.get('page', 1)) - 1
    rows_per_page = int(request.values.get('rows', 20))
    
    fields_convert_map = {'task_id':task_id, 'risk_if':boolean_convert, 
        'advanced_if':boolean_convert, 'tx':tx, 't_delay':t_delay,
        'milestone_delay_rate':milestone_delay_rate,
        'milestone_change_rate':milestone_change_rate}
    
    model = 'tasks_develop_report_detail'
    _id = '%s_table' % model
    view =  ListView(model, condition=condition, rows_per_page=rows_per_page, 
        pageno=pageno, id=_id, fields_convert_map=fields_convert_map)
        
    if 'download' in request.GET:
#        def stage(value, obj):
#            return unicode(get_choice(settings.PARA.TASK_STAGE[obj.type], value), 'utf8')
        fields_convert_map = {}
        return view.download('tasks.xls', action='download', timeout=0,
            fields_convert_map=fields_convert_map)
    else:
        data = {'query_form':query_view.form}
        return g_views.generic_list(model, template='charts_manage_tasks_develop.html',
            view=view, data=data, json_result=True)    



def get_work_total_list_view(c):
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from uliweb.utils.common import get_var
    from uliweb.core.html import Tag
    from charts.views import charts_manage_requirements
    from uliweb import settings
    from uliweb.utils.common import get_choice

    pageno = int(request.GET.get('pageno', 0))
    
    Req_report = get_model('manage_requirement_week')
    Ass_report = get_model('assignment_report_detail')
    Task_report = get_model('tasks_develop_report_detail')    
    tx = get_model('tx')
    zx = get_model('kfzx')
    sys = get_model('system')
    Req = get_model('requirement')
    
    query = None
    con_req = None
    con_ass = None
    con_task = None
    
    con_req = (Req_report.c.req_id == Req.c.id) & con_req
    
    id_type = c['id_type']
#    con_ass = (Ass_report.c.id_type == c['id_type']) & con_ass
        
    report_type = '1'
    if c.get('type'):
        report_type = str(c['type'])
        con_req = (Req_report.c.report_type == (c['type'])) & con_req
        con_ass = (Ass_report.c.report_type == (c['type'])) & con_ass
        con_task = (Task_report.c.report_type == (c['type'])) & con_task
    #取最近的报表日期
    last_date = None
    con = None
    if c.get('begin_date'):
        con = (Req_report.c.report_date >= c['begin_date']) & con
    if c.get('end_date'):
        con = (Req_report.c.report_date <= c['end_date']) & con
    con = (Req_report.c.report_type == report_type) & con
    sql_date = select([func.max(Req_report.c.report_date)],con)
    sql_date_rs = sql_date.execute()
    for row in sql_date_rs:
        last_date = row[0]
    
    con_req = (Req_report.c.report_date == last_date) & con_req
    con_ass = (Ass_report.c.report_date == last_date) & con_ass
    con_task = (Task_report.c.report_date == last_date) & con_task
    
    system = None
    if c.get('system'):
        system = c['system']
        con_req = (Req.c.main_sys == (c['system'])) & con_req
        con_ass = (Ass_report.c.system == (c['system'])) & con_ass
        con_task = (Task_report.c.task_sys == (c['system'])) & con_task
    
    #以下注释是为了解决项目管理处查询报表等问题，20110629
#    if request.user.department == '1':
#        con_req = (Req_report.c.tx == request.user._tx_) & con_req
#        con_ass = (Ass_report.c.tx == request.user._tx_) & con_ass
#        con_task = (Task_report.c.tx == request.user._tx_) & con_task
        
#    elif request.user.department == '2':
    if request.user.department == '2':
        if request.user.is_gm:
            con_req = Req.kfzxs.in_(request.user._kfzx_) & con_req
            con_ass = (Ass_report.c.kfzx == request.user._kfzx_) & con_ass
            con_task = (Task_report.c.kfzx== request.user._kfzx_)  & con_task
        else:
            ids = request.user.users_systems.ids()
            con_req = Req.systems.in_(*ids) & con_req
            con_ass = Ass_report.c.system.in_(ids) & con_ass
            con_task = Task_report.c.task_sys.in_(ids) & con_task
                    
    if id_type == '1':
        req_dimention = 'manage_requirement_week.tx'
        ass_dimention = 'assignment_report_detail.tx'
        task_dimention = 'tasks_develop_report_detail.tx'
    elif id_type == '2':
        req_dimention = 'manage_requirement_week.kfzx'
        ass_dimention = 'assignment_report_detail.kfzx'
        task_dimention = 'tasks_develop_report_detail.kfzx'
    elif id_type == '3':
        req_dimention = 'requirement.main_sys'                
        ass_dimention = 'assignment_report_detail.system'
        task_dimention = 'tasks_develop_report_detail.task_sys'
            
    sql_requirement = select([req_dimention,
                 func.sum(Req_report.c.t_project),
                 func.sum(Req_report.c.t_nproject),
                 func.sum(Req_report.c.t_nassignment),
                 func.sum(Req_report.c.t_undetermined),
                 func.sum(Req_report.c.t_delay_flag)],
                 con_req).group_by(req_dimention)                 
    sql_requirement_rs = list(sql_requirement.execute())
    
    sql_assignment = select([ass_dimention,
                func.sum(Ass_report.c.t_implement),
                func.sum(Ass_report.c.t_js),
                func.sum(Ass_report.c.t_finish),
                func.sum(Ass_report.c.t_delay),
                func.sum(Ass_report.c.t_status),
                func.sum(Ass_report.c.t_task_delay),                
                Ass_report.c.type],
                con_ass).group_by(ass_dimention,Ass_report.c.type)
    sql_assignment_rs = list(sql_assignment.execute())

    sql_task = select([task_dimention,
                func.sum(Task_report.c.t_implement),
                func.sum(Task_report.c.t_finish),
                func.sum(Task_report.c.t_delay),
                func.sum(Task_report.c.t_status),
                func.sum(Task_report.c.milestone_delay_num),
                func.sum(Task_report.c.milestone_change_num), 
                func.sum(Task_report.c.t_version),],               
                con_task).group_by(task_dimention)
    sql_task_rs = list(sql_task.execute())
    
    data=[]

    dimention = ''
    if id_type == '1':
        dimention = tx
    elif id_type == '2':
        dimention = zx
    elif id_type == '3':
        dimention = sys
        
    if (id_type=='1'): 
        for row in dimention.all():
            x = {'tx':0,'total':0,'t_project':0,'t_nproject':0,'t_nassignment':0,'t_undetermined':0,'t_delay_flag':0,'delay_rate':0,
                'project_total':0,'project_implement':0,'project_js':0,'project_finish':0,'project_delay':0,'project_delay_rate':0,
                'project_task_delay':0,'project_task_delay_rate':0,'project_unnomal':0,
                'nproject_total':0,'nproject_implement':0,'nproject_finish':0,'nproject_delay':0,
                'nproject_delay_rate':0,'nproject_task_delay':0,'nproject_task_delay_rate':0,'nproject_unnomal':0,
                'task_total':0,'task_implement':0,'task_finish':0,'task_version':0,
                'task_delay':0,'task_delay_rate':0,'report_type':0,'report_date':0,
                }
            x['tx'] = row.id     
            for i in sql_requirement_rs:
                #准开发需求
                if i[0]==row.id:
                    x['total']=i[1]+i[2]+i[3]+i[4]
                    x['t_project']=i[1]
                    x['t_nproject']=i[2]
                    x['t_nassignment']=i[3]
                    x['t_undetermined']=i[4]
                    x['t_delay_flag']=i[5]
                    if x['total']>0:
                        x['delay_rate']=float(i[5])/float(x['total'])

            for j in sql_assignment_rs:
                #项目
                if (j[0]==row.id)and(j[7]=='1'):
                    t = j[1]+j[2]+j[3]
                    x['project_total']=j[1]+j[2]+j[3]
                    x['project_implement']=j[1]
                    x['project_js']=j[2]
                    x['project_finish']=j[3]
                    x['project_delay']=j[4]
                    if t>0:
                        x['project_delay_rate']=float(j[4])/float(t)
                    x['project_task_delay']=j[6]
                    if t>0:
                        x['project_task_delay_rate']=float(j[6])/float(t)
                    x['project_unnomal']=j[5]

            for k in sql_assignment_rs:
                #非项目
                if (k[0]==row.id)and(k[7]=='3'):
                    t = k[1]+k[3]
                    x['nproject_total']=k[1]+k[3]
                    x['nproject_implement']=k[1]
                    x['nproject_finish']=k[3]
                    x['nproject_delay']=k[4]
                    if t>0:
                        x['nproject_delay_rate']=float(k[4])/float(t)
                    x['nproject_task_delay']=k[6]
                    if t>0:
                        x['nproject_task_delay_rate']=float(k[6])/float(t)
                    x['nproject_unnomal']=k[5]
            for r in sql_task_rs:
                #任务
                if r[0]==row.id:
                    t = r[1]+r[2]
                    x['task_total']=r[1]+r[2]
                    x['task_implement']=r[1]
                    x['task_finish']=r[2]
                    x['task_version']=r[7]
                    x['task_delay']=r[3]
                    if t>0:
                        x['task_delay_rate']=float(r[3])/float(t)
            x['report_type'] = report_type
            x['report_date'] = last_date
        
            data.append(x)
    
    if (id_type=='2'):                    
        for row in dimention.filter(and_(zx.c.type == '1')):
            x = {'tx':0,'total':0,'t_project':0,'t_nproject':0,'t_nassignment':0,'t_undetermined':0,'t_delay_flag':0,'delay_rate':0,
                'project_total':0,'project_implement':0,'project_js':0,'project_finish':0,'project_delay':0,'project_delay_rate':0,
                'project_task_delay':0,'project_task_delay_rate':0,'project_unnomal':0,
                'nproject_total':0,'nproject_implement':0,'nproject_finish':0,'nproject_delay':0,
                'nproject_delay_rate':0,'nproject_task_delay':0,'nproject_task_delay_rate':0,'nproject_unnomal':0,
                'task_total':0,'task_implement':0,'task_finish':0,'task_version':0,
                'task_delay':0,'task_delay_rate':0,'report_type':0,'report_date':0,
                }
            x['tx'] = row.id     
            for i in sql_requirement_rs:
                #准开发需求
                if i[0]==row.id:
                    x['total']=i[1]+i[2]+i[3]+i[4]
                    x['t_project']=i[1]
                    x['t_nproject']=i[2]
                    x['t_nassignment']=i[3]
                    x['t_undetermined']=i[4]
                    x['t_delay_flag']=i[5]
                    if x['total']>0:
                        x['delay_rate']=float(i[5])/float(x['total'])
    
            for j in sql_assignment_rs:
                #项目
                if (j[0]==row.id)and(j[7]=='1'):
                    t = j[1]+j[2]+j[3]
                    x['project_total']=j[1]+j[2]+j[3]
                    x['project_implement']=j[1]
                    x['project_js']=j[2]
                    x['project_finish']=j[3]
                    x['project_delay']=j[4]
                    if t>0:
                        x['project_delay_rate']=float(j[4])/float(t)
                    x['project_task_delay']=j[6]
                    if t>0:
                        x['project_task_delay_rate']=float(j[6])/float(t)
                    x['project_unnomal']=j[5]
    
            for k in sql_assignment_rs:
                #非项目
                if (k[0]==row.id)and(k[7]=='3'):
                    t = k[1]+k[3]
                    x['nproject_total']=k[1]+k[3]
                    x['nproject_implement']=k[1]
                    x['nproject_finish']=k[3]
                    x['nproject_delay']=k[4]
                    if t>0:
                        x['nproject_delay_rate']=float(k[4])/float(t)
                    x['nproject_task_delay']=k[6]
                    if t>0:
                        x['nproject_task_delay_rate']=float(k[6])/float(t)
                    x['nproject_unnomal']=k[5]
            for r in sql_task_rs:
                #任务
                if r[0]==row.id:
                    t = r[1]+r[2]
                    x['task_total']=r[1]+r[2]
                    x['task_implement']=r[1]
                    x['task_finish']=r[2]
                    x['task_version']=r[7]
                    x['task_delay']=r[3]
                    if t>0:
                        x['task_delay_rate']=float(r[3])/float(t)
            x['report_type'] = report_type
            x['report_date'] = last_date
        
            data.append(x)
    
    if id_type=='3':            
        for row in dimention.filter(sys.c.kfzx!=8).all().order_by(sys.c.sys_type,sys.c.kfzx):
            x = {'tx':0,'name':0,'type':0,'zx':0,'total':0,'t_project':0,'t_nproject':0,'t_nassignment':0,'t_undetermined':0,'t_delay_flag':0,'delay_rate':0,
                'project_total':0,'project_implement':0,'project_js':0,'project_finish':0,'project_delay':0,'project_delay_rate':0,
                'project_task_delay':0,'project_task_delay_rate':0,'project_unnomal':0,
                'nproject_total':0,'nproject_implement':0,'nproject_finish':0,'nproject_delay':0,
                'nproject_delay_rate':0,'nproject_task_delay':0,'nproject_task_delay_rate':0,'nproject_unnomal':0,
                'task_total':0,'task_implement':0,'task_finish':0,'task_version':0,
                'task_delay':0,'task_delay_rate':0,'report_type':0,'report_date':0,
                }
            
            x['tx']=row.id
            x['name']=row.name
            x['type']=unicode(dict(settings.PARA.SYSTEM_TYPE)[row.sys_type],'utf-8')
            x['zx']=row.kfzx.name                
            for i in sql_requirement_rs:
                #准开发需求
                if i[0]==row.id:
                    x['total']=i[1]+i[2]+i[3]+i[4]
                    x['t_project']=i[1]
                    x['t_nproject']=i[2]
                    x['t_nassignment']=i[3]
                    x['t_undetermined']=i[4]
                    x['t_delay_flag']=i[5]
                    if x['total']>0:
                        x['delay_rate']=float(i[5])/float(x['total'])
                    
            for j in sql_assignment_rs:
                #项目
                if (j[0]==row.id)and(j[7]=='1'):
                    t = j[1]+j[2]+j[3]
                    x['project_total']=j[1]+j[2]+j[3]
                    x['project_implement']=j[1]
                    x['project_js']=j[2]
                    x['project_finish']=j[3]
                    x['project_delay']=j[4]
                    if t>0:
                        x['project_delay_rate']=float(j[4])/float(t)
                    x['project_task_delay']=j[6]
                    if t>0:
                        x['project_task_delay_rate']=float(j[6])/float(t)
                    x['project_unnomal']=j[5]
                    
            for k in sql_assignment_rs:
                #非项目
                if (k[0]==row.id)and(k[7]=='3'):
                    t = k[1]+k[3]
                    x['nproject_total']=k[1]+k[3]
                    x['nproject_implement']=k[1]
                    x['nproject_finish']=k[3]
                    x['nproject_delay']=k[4]
                    if t>0:
                        x['nproject_delay_rate']=float(k[4])/float(t)
                    x['nproject_task_delay']=k[6]
                    if t>0:
                        x['nproject_task_delay_rate']=float(k[6])/float(t)
                    x['nproject_unnomal']=k[5]
            for r in sql_task_rs:
                #任务
                if r[0]==row.id:
                    t = r[1]+r[2]
                    x['task_total']=r[1]+r[2]
                    x['task_implement']=r[1]
                    x['task_finish']=r[2]
                    x['task_version']=r[7]
                    x['task_delay']=r[3]
                    if t>0:
                        x['task_delay_rate']=float(r[3])/float(t)
            x['report_type'] = report_type
            x['report_date'] = last_date
                        
            #按系统维度查询时，只显示查询的系统
            if (system == row.id) or (system == None) :
                data.append(x)


    #转换准开发需求相关链接,key为header字段名, value为convert函数参数
    dim = None
    if id_type == '1':
        dim = 'tx'
    elif id_type == '2':
        dim = 'main_zx'
    elif id_type == '3':
        dim = 'main_sys'

    ss = {
        'total':{},         
        't_nproject':{'t_nproject':1}, 
        't_project':{'t_project':1},
        't_nassignment':{'t_nassignment':1},
        't_undetermined':{'t_undetermined':1},
        't_delay_flag':{'t_delay_flag':1},
        }
    fields_convert_map = {}
    for k, v in ss.items():
        def _f(value, obj, v=v):
            v[dim] = obj['tx']
            if system:
                v['main_sys'] = system
            return str(Tag('a', value, href=url_for(charts_manage_requirements, 
                report_date=last_date,report_type=report_type, flag=1,
                **v), target='_blank'))
        fields_convert_map[k] = _f

    #转换项目相关链接,key为header字段名, value为convert函数参数
    dim_project = None
    if id_type == '1':
        dim_project = 'tx'
    elif id_type == '2':
        dim_project = 'kfzx'
    elif id_type == '3':
        dim_project = 'system'    
    tt = {
        'project_total':{'type':'1'},         
        'project_implement':{'t_implement':1,'type':'1'}, 
        'project_js':{'t_js':1,'type':'1'}, 
        'project_finish':{'t_finish':1,'type':'1'},
        'project_delay':{'t_delay':1,'type':'1'},
        'project_unnomal':{'t_status':1,'type':'1'},
        'project_task_delay':{'t_task_delay':1,'type':'1'},
        'nproject_total':{'type':'3'},         
        'nproject_implement':{'t_implement':1,'type':'3'}, 
        'nproject_finish':{'t_finish':1,'type':'3'},
        'nproject_delay':{'t_delay':1,'type':'3'},
        'nproject_unnomal':{'t_status':1,'type':'3'},
        'nproject_task_delay':{'t_task_delay':1,'type':'3'},
        }
    for k, v in tt.items():
        def _f(value, obj, v=v):
            v[dim_project] = obj['tx']
            if system:
                v['system'] = system
            return str(Tag('a', value, href=url_for(charts_manage_assignments, 
                report_date=last_date,report_type=report_type,
                **v), target='_blank'))
        fields_convert_map.update({k:_f})
    

    #转换开发任务相关链接,key为header字段名, value为convert函数参数
    dim_task = None
    if id_type == '1':
        dim_task = 'tx'
    elif id_type == '2':
        dim_task = 'kfzx'
    elif id_type == '3':
        dim_task = 'task_sys'
    xx = {
        'task_total':{},             
        'task_implement':{'t_implement':1}, 
        'task_finish':{'t_finish':1},
        'task_version':{'t_version':1},
        'task_delay':{'t_delay':1},
        'task_milestone_delay_num':{'milestone_delay_num':1},
        'task_milestone_change_num':{'milestone_change_num':1}
        }
    for k, v in xx.items():
        def _f(value, obj, v=v):
            v[dim_task] = obj['tx']
            if system:
                v['task_sys'] = system            
            return str(Tag('a', value, href=url_for(charts_manage_tasks_develop, 
                report_date=last_date,report_type=report_type,
                **v),target='_blank'))
        fields_convert_map.update({k:_f})
    
    def i_tx(value, obj):
        if dimention == sys:
            return sys.get(value).sys_id
        rs_sel = select([dimention.c.name],and_(dimention.c.id == value))
            
        rs_sel_r = rs_sel.execute()
        name = ''
        for row in rs_sel_r:
            name, = row
        #res_tx = tx.get(tx.c.id == value)
        return name

    def i_report_type(value, obj):
        return dict(settings.PARA.REPORT_TYPE)[value]
    
    yy = ['delay_rate','project_delay_rate','project_task_delay_rate','nproject_delay_rate',
        'nproject_task_delay_rate','task_delay_rate']
    for i in yy:
        def _f(value,obj):
            return str(round(value,4)*100)+'%'
        fields_convert_map.update({i:_f})
        
    def delay_rate(value,obj):
        return str(round(value,4)*100)+'%'
    
    fields_convert_map.update({'tx':i_tx})
    fields_convert_map.update({'report_type':i_report_type})
    
    def avg1(v,totals):
        if totals['total']==0:
            return '0.0%'
        return str(round(float(totals['t_delay_flag'])/float(totals['total']),4)*100)+'%'
    def avg2(v,totals):
        if totals['project_total']==0:
            return '0.0%'
        return str(round(float(totals['project_delay'])/float(totals['project_total']),4)*100)+'%'
    def avg3(v,totals):
        if totals['project_total']==0:
            return '0.0%'
        return str(round(float(totals['project_task_delay'])/float(totals['project_total']),4)*100)+'%'
    def avg4(v,totals):
        if totals['nproject_total']==0:
            return '0.0%'
        return str(round(float(totals['nproject_delay'])/float(totals['nproject_total']),4)*100)+'%'
    def avg5(v,totals):
        if totals['nproject_total']==0:
            return '0.0%'
        return str(round(float(totals['nproject_task_delay'])/float(totals['nproject_total']),4)*100)+'%'
    def avg6(v,totals):
        if totals['task_total']==0:
            return '0.0%'
        return str(round(float(totals['task_delay'])/float(totals['task_total']),4)*100)+'%'
    
    total_fields={'total_field_name':'合计', 
        'fields':['total',
            't_project',
            't_nproject',
            't_nassignment',
            't_undetermined',
            't_delay_flag',
            {'name':'delay_rate','render':avg1},
            'project_total',
            'project_implement',
            'project_js',
            'project_finish',
            'project_delay',
            {'name':'project_delay_rate','render':avg2},            
            'project_task_delay',
            {'name':'project_task_delay_rate','render':avg3},            
            'project_unnomal',
            'nproject_total',
            'nproject_implement',
            'nproject_finish',
            'nproject_delay',
            {'name':'nproject_delay_rate','render':avg4},                        
            'nproject_task_delay',
            {'name':'nproject_task_delay_rate','render':avg5},            
            'nproject_unnomal',
            'task_total',
            'task_implement',
            'task_finish',
            'task_version',
            'task_delay',
            {'name':'task_delay_rate','render':avg6},            
            ]}
    
    pageno = int(request.values.get('page', 1)) - 1
    rows_per_page = int(request.values.get('rows', settings.get_var('PARA/ROWS_PER_PAGE', 10)))
    
    if id_type == '1':
                
        view =  SimpleListView(settings.PARA.work_report, data,total_fields=total_fields,
            pagination=True, fields_convert_map=fields_convert_map, pageno=pageno,
            id='req_table_detail', rows_per_page=rows_per_page)
    elif id_type == '2':        
        view =  SimpleListView(settings.PARA.work_report_zx, data,total_fields=total_fields,
            pagination=True, fields_convert_map=fields_convert_map, pageno=pageno,
            id='req_table_detail', rows_per_page=rows_per_page)
    elif id_type == '3':        
        view =  SimpleListView(settings.PARA.work_report_system, data,total_fields=total_fields,
            pagination=True, fields_convert_map=fields_convert_map, pageno=pageno,
            id='req_table_detail', rows_per_page=rows_per_page)

    return view
#管理类报表----本周在建事项
@expose('/charts/manage/work/total')
def charts_manage_work_total():
    from query import create_manage_work_total_query
    
    query = create_manage_work_total_query(url_for(charts_manage_work_total))
    condition = query.run()
    view = get_work_total_list_view(condition)
    if 'download' in request.GET:
#        fields_convert_map = {}
        return view.download('implement_summary.xls', action='download', 
            timeout=0)
    elif 'data' in request.values:
#        result = view.run(head=False, body=True)
#        return json(result)
        return json(view.json())
    else:
        result = view.run(head=True, body=True)
        result['table'] = view
        result.update({'query_form':query.form})
        return result

#管理类报表----任务所属阶段平均耗时
@expose('/charts/manage/tasks/stage/avgtime')
def charts_manage_tasks_stage_avgtime():

    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from uliweb.utils.common import get_var
    from uliweb.core.html import Tag
    from charts.views import charts_manage_requirements
    from uliweb import settings
    from uliweb.utils.common import get_choice
    from query import create_manage_task_avgtime_query
    import copy
    
    query = create_manage_task_avgtime_query(url_for(charts_manage_tasks_stage_avgtime))
    c = query.run()
    pageno = int(request.GET.get('pageno', 0))
    #--------------------------------------------------------------#
    req_detail = get_model('tasks_details')
#    Task_detail_tmp = get_model('tmp_tasks_details')
    req_dev = get_model('tasks_develop')

    Kfzx = get_model('kfzx')
    #   sys = get_model('system')
    #   Req = get_model('requirement')
    
    condition = None
    if c.get('begin_date'):
#        condition = (Task_detail_tmp.c.begin_time >= c['begin_date']) & condition
        condition = (req_dev.c.begin_time >= c['begin_date']) & condition
    if c.get('end_date'):
#        condition = (Task_detail_tmp.c.begin_time <= c['end_date']) & condition
        condition = (req_dev.c.begin_time <= c['end_date']) & condition
#-----------------------------------------------#
    sql_sel = select([req_detail.c.task_id,
                        func.sum((func.datediff(req_detail.c.end_time,req_detail.c.begin_time))+1),
                        req_detail.c.stage,
                        req_dev.c.kfzx],
#                        req_detail.c.tx,
#                        func.min(req_detail.c.begin_time),
#                        func.max(req_detail.c.end_time)],
                        and_(req_detail.c.task_id.in_(
                                                select([req_dev.c.task_id],
                                                    and_(or_(req_dev.c.status != '4',
                                                        req_dev.c.status != '5'),
                                                        condition))),
                            req_detail.c.task_id == req_dev.c.task_id)
                        ).group_by(req_detail.c.task_id,req_detail.c.stage)
    
    rs_sql_sel =  sql_sel.execute()

#-----------------------------------------------#            
#    sql_task = select([func.avg(Task_detail_tmp.c.num),
#    sql_task = select([func.sum(Task_detail_tmp.c.num),
#                    Task_detail_tmp.c.kfzx,
#                    Task_detail_tmp.c.stage,
#                    func.count(Task_detail_tmp.c.task_id)],and_(condition)
#                    ).group_by(Task_detail_tmp.c.kfzx,Task_detail_tmp.c.stage)                 
#    
#    rs_sql_task = sql_task.execute()

    data = []
    header = []
    header.append({'name':'kfzx','verbose_name':'中心', 'width':70})
#    header.append({'name':'00','verbose_name':'任务所属阶段(单位：天)/未开始', 'width':70})
    header.append({'name':'xqfx','verbose_name':'任务所属阶段(平均耗时单位：天)/需求分析/填报率', 'width':70})
    header.append({'name':'02','verbose_name':'任务所属阶段(平均耗时单位：天)/需求分析/平均耗时', 'width':70})
    header.append({'name':'gysj','verbose_name':'任务所属阶段(平均耗时单位：天)/概要设计/填报率', 'width':70})
    header.append({'name':'03','verbose_name':'任务所属阶段(平均耗时单位：天)/概要设计/平均耗时', 'width':70})
    header.append({'name':'xxsj','verbose_name':'任务所属阶段(平均耗时单位：天)/详细设计/填报率', 'width':70})
    header.append({'name':'04','verbose_name':'任务所属阶段(平均耗时单位：天)/详细设计/平均耗时', 'width':70})
    header.append({'name':'kf','verbose_name':'任务所属阶段(平均耗时单位：天)/开发/填报率', 'width':70})
    header.append({'name':'05','verbose_name':'任务所属阶段(平均耗时单位：天)/开发/平均耗时', 'width':70})
#    header.append({'name':'06','verbose_name':'任务所属阶段(单位：天)/内部测试', 'width':70})
#    header.append({'name':'07','verbose_name':'任务所属阶段(单位：天)/功能集成测试', 'width':70})
#    header.append({'name':'08','verbose_name':'任务所属阶段(单位：天)/非功能集成测试', 'width':70})
#    header.append({'name':'09','verbose_name':'任务所属阶段(单位：天)/用户测试', 'width':70})
#    header.append({'name':'10','verbose_name':'任务所属阶段(单位：天)/版本验证测试', 'width':70})
#    
    header.append({'name':'cstbl','verbose_name':'任务所属阶段(平均耗时单位：天)/测试/填报率', 'width':70})
    header.append({'name':'CS','verbose_name':'任务所属阶段(平均耗时单位：天)/测试/平均耗时', 'width':70})
    
    header.append({'name':'bbtc','verbose_name':'任务所属阶段(平均耗时单位：天)/版本投产/填报率', 'width':70})
    header.append({'name':'11','verbose_name':'任务所属阶段(平均耗时单位：天)/版本投产/平均耗时', 'width':70})
#    header.append({'name':'FN','verbose_name':'任务所属阶段(单位：天)/完成', 'width':70})
    
    #   task_stage_num = {'00':1,'02':2,'03':3,'04':4,'05':5,'06':6,'07':7,'08':8,'09':9,'10':10,'11':11,'FN':12}
    display_stage = {'00':'00','02':'02','03':'03','04':'04','05':'05',
                     '06':'CS','07':'CS','08':'CS','09':'CS','10':'CS',
                     '11':'11','FN':'FN'}
    stage_tbl = {'00':'wks','02':'xqfx','03':'gysj','04':'xxsj','05':'kf','CS':'cstbl','11':'bbtc'}
    #初始化后的字典x用来记录按开发中心维度统计的各阶段耗费天数累计，最后存放统计计算结果
    x = {}
    for row in Kfzx.all():
#        row = list(row)
        x[str(row.id)] = {'kfzx':row.id,'00':0,'wks':'','02':0,'xqfx':'','03':0,'gysj':'',
                          '04':0,'xxsj':'','05':0,'kf':'',
                           '06':0,'07':0,'08':0,'09':0,'10':0,'11':0,'bbtc':'','FN':0,
                            'CS':0,'cstbl':''}
    #初始化后的字典y用来记录按开发中心维度统计的各阶段有值的记录天数累计
    y = {}

    for row in Kfzx.all():
#        row = list(row)
        y[str(row.id)] = {'kfzx':row.id,'00':0,'wks':'','02':0,'xqfx':'','03':0,'gysj':'',
                          '04':0,'xxsj':'','05':0,'kf':'',
                           '06':0,'07':0,'08':0,'09':0,'10':0,'11':0,'bbtc':'','FN':0,
                            'CS':0,'cstbl':''}

    #初始化后的字典z用来记录按display_stage分类的各阶段耗费天数除以记录数
    z = {}
    for row in Kfzx.all():
#        row = list(row)
        z[str(row.id)] = {'kfzx':row.id,'00':0,'wks':'','02':0,'xqfx':'','03':0,'gysj':'',
                           '04':0,'xxsj':'','05':0,'kf':'',
                           '06':0,'07':0,'08':0,'09':0,'10':0,'11':0,'bbtc':'','FN':0,
                           'CS':0,'cstbl':''}
    tbl_y = {}
    for row in Kfzx.all():
#        row = list(row)
        tbl_y[str(row.id)] = {'kfzx':row.id,'00':0,'wks':'','02':0,'xqfx':'','03':0,'gysj':'',
                           '04':0,'xxsj':'','05':0,'kf':'',
                           '06':0,'07':0,'08':0,'09':0,'10':0,'11':0,'bbtc':'','FN':0,
                           'CS':0,'cstbl':''}

    total_avg_x = {}
    total_avg_x = {'kfzx':99994,'00':0,'wks':'','02':0,'xqfx':'','03':0,'gysj':'',
                          '04':0,'xxsj':'','05':0,'kf':'',
                           '06':0,'07':0,'08':0,'09':0,'10':0,'11':0,'bbtc':'','FN':0,
                           'CS':0,'cstbl':''}

    total_avg_y = {}
    total_avg_y = {'kfzx':99994,'00':0,'wks':'','02':0,'xqfx':'','03':0,'gysj':'',
                           '04':0,'xxsj':'','05':0,'kf':'',
                           '06':0,'07':0,'08':0,'09':0,'10':0,'11':0,'bbtc':'','FN':0,
                           'CS':0,'cstbl':''}


    #累计x和y中各阶段的值
#    for row in rs_sql_task:
    for row in rs_sql_sel:
#        num,kfzx,stage,line_count = row
        task_id,num,stage,kfzx = row
    #       x = [0,0,0,0,0,0,0,0,0,0,0,0,0]
    #       x[0] = kfzx
        if stage != '' and stage != None and kfzx != None:
#            print 'stage',stage,num,kfzx,stage,line_count
#            x[str(kfzx)][display_stage[stage]] += int(num)
            if stage in ['06','05']:
                x[str(kfzx)]['05'] += int(num)
                total_avg_x['05'] += int(num)
            elif stage in ['07','08','09','10']:
                x[str(kfzx)][stage] += int(num)
                total_avg_x['CS'] += int(num)            
            else:
                x[str(kfzx)][stage] += int(num)
                total_avg_x[stage] += int(num)
            
#            y[str(kfzx)][display_stage[stage]] += int(line_count)
#            y[str(kfzx)][stage] += int(line_count)
#            y[str(kfzx)][stage] += 1
#            total_avg_y[stage] += int(line_count)
#            total_avg_y[stage] += 1
#            print '$$$$$$$$$$$$$$$',x[str(kfzx)][display_stage[stage]],y[str(kfzx)][display_stage[stage]]
    sql_task_KF = select([func.count(func.distinct(req_dev.c.task_id)),
                        req_dev.c.kfzx,
                        req_detail.c.stage],
                        and_(
                        req_dev.c.task_id==req_detail.c.task_id,
                        req_dev.c.status != '4',
                        req_dev.c.status != '5',
                        condition)
                        ).group_by(req_dev.c.kfzx,req_detail.c.stage)
    
    rs_sql_task_KF = sql_task_KF.execute()
    for row in rs_sql_task_KF:
        count,kfzx,stage =row
        if not kfzx or not stage:
            continue
#        print 'stage',count,kfzx,stage
        if y[str(kfzx)].has_key(stage):
            y[str(kfzx)][stage] = count
            total_avg_y[stage] += count
    #下面这个查询和for循环处理填报率分母问题，
    sql_task_KF = select([func.count(func.distinct(req_dev.c.task_id)),
                        req_dev.c.kfzx,
                        req_dev.c.stage],
                        and_(
                        req_dev.c.status != '4',
                        req_dev.c.status != '5',
                        condition)
                        ).group_by(req_dev.c.kfzx,req_dev.c.stage)
    
    rs_sql_task_KF = sql_task_KF.execute()
    task_total_all = {'02':0,'03':0,'04':0,'05':0,'CS':0,'11':0}
    for row in rs_sql_task_KF:
        count,kfzx,stage = row
        if not kfzx:
            continue
        if stage =='02':
            tbl_y[str(kfzx)]['02']+=count
            task_total_all['02'] +=count 
        if stage =='03':
            tbl_y[str(kfzx)]['02']+=count
            tbl_y[str(kfzx)]['03']+=count
            task_total_all['02'] +=count
            task_total_all['03'] +=count
        if stage =='04':
            tbl_y[str(kfzx)]['02']+=count
            tbl_y[str(kfzx)]['03']+=count
            tbl_y[str(kfzx)]['04']+=count
            task_total_all['02'] +=count
            task_total_all['03'] +=count
            task_total_all['04'] +=count
        if stage =='05' or stage =='06':#开发和内部测试均计算为开发阶段
            tbl_y[str(kfzx)]['02']+=count
            tbl_y[str(kfzx)]['03']+=count
            tbl_y[str(kfzx)]['04']+=count
            tbl_y[str(kfzx)]['05']+=count
            task_total_all['02'] +=count
            task_total_all['03'] +=count
            task_total_all['04'] +=count
            task_total_all['05'] +=count
        if stage =='07' or stage =='08' or stage =='09' or stage =='10':#测试阶段
            tbl_y[str(kfzx)]['02']+=count
            tbl_y[str(kfzx)]['03']+=count
            tbl_y[str(kfzx)]['04']+=count
            tbl_y[str(kfzx)]['05']+=count
            tbl_y[str(kfzx)]['CS']+=count
            task_total_all['02'] +=count
            task_total_all['03'] +=count
            task_total_all['04'] +=count
            task_total_all['05'] +=count
            task_total_all['CS'] +=count
        if stage =='11':#版本投产阶段
            tbl_y[str(kfzx)]['02']+=count
            tbl_y[str(kfzx)]['03']+=count
            tbl_y[str(kfzx)]['04']+=count
            tbl_y[str(kfzx)]['05']+=count
            tbl_y[str(kfzx)]['CS']+=count
            tbl_y[str(kfzx)]['11']+=count
            task_total_all['02'] +=count
            task_total_all['03'] +=count
            task_total_all['04'] +=count
            task_total_all['05'] +=count
            task_total_all['CS'] +=count
            task_total_all['11'] +=count
        if stage =='FN':#版本投产阶段
            tbl_y[str(kfzx)]['02']+=count
            tbl_y[str(kfzx)]['03']+=count
            tbl_y[str(kfzx)]['04']+=count
            tbl_y[str(kfzx)]['05']+=count
            tbl_y[str(kfzx)]['CS']+=count
            tbl_y[str(kfzx)]['11']+=count
            task_total_all['02'] +=count
            task_total_all['03'] +=count
            task_total_all['04'] +=count
            task_total_all['05'] +=count
            task_total_all['CS'] +=count
            task_total_all['11'] +=count
        
    
#    sql_task_KF = select([func.count(func.distinct(req_dev.c.task_id)),
#                        req_dev.c.kfzx],
#                        and_(
#                        req_dev.c.status != '4',
#                        req_dev.c.status != '5',
#                        condition)
#                        ).group_by(req_dev.c.kfzx)
#    print 'sql_task_KF',sql_task_KF
#    rs_sql_task_KF = sql_task_KF.execute()
#    total_kfzx ={}
##    task_total_all = 0
#    for row in rs_sql_task_KF:
#        count,kfzx = row
#        total_kfzx[str(kfzx)]=count
#        print 'count',count,kfzx
#        task_total_all += count
    sql_task_KF = select([func.count(func.distinct(req_dev.c.task_id)),
                        req_dev.c.kfzx],
                        and_(req_detail.c.stage.in_(['05','06']),
                        req_dev.c.task_id==req_detail.c.task_id,
                        req_dev.c.status != '4',
                        req_dev.c.status != '5',
                        condition)
                        ).group_by(req_dev.c.kfzx)
    
    rs_sql_task_KF = sql_task_KF.execute()
    total_avg_y['05']=0
    total_avg_y['05'] = 0
    for row in rs_sql_task_KF:
        count,kfzx =row
        if not kfzx:
            continue
        y[str(kfzx)]['05'] = count
        total_avg_y['05'] += count
    
#    print 'y',y
#    print 'tbl_y',tbl_y
    #按照合并阶段方式计算结果，存放在z中
    for row in x.items():
        t,v = row
        for aa in v.keys():
            if aa!= 'kfzx' and aa !='CS' and display_stage.has_key(aa): 
                 z[t][display_stage[aa]] += v[aa]
#    sql_task_CS = select([Task_detail_tmp.c.kfzx,
#                         #Task_detail_tmp.c.stage,
#                         func.count(Task_detail_tmp.c.task_id)],
#                         and_(Task_detail_tmp.c.stage.in_(['06','07','08','09','10']))
#                         ).group_by(Task_detail_tmp.c.kfzx)


    sql_task_CS = select([func.count(func.distinct(req_dev.c.task_id)),
#    sql_task_CS = select([func.count(req_dev.c.task_id),
                        req_dev.c.kfzx],
                        and_(req_detail.c.stage.in_(['07','08','09','10']),
                        req_dev.c.task_id==req_detail.c.task_id,
                        req_dev.c.status != '4',
                        req_dev.c.status != '5',
                        condition)
                        ).group_by(req_dev.c.kfzx)

#    print 'sql_task_CS',sql_task_CS
    rs_sql_task_CS = sql_task_CS.execute()
    cs_count = {}
    total_avg_y['CS'] = 0
    for row in rs_sql_task_CS:
#         kfzx,count =row
         count,kfzx =row
         if not kfzx:
            continue
         cs_count[str(kfzx)] = count
         total_avg_y['CS'] += count
    #计算每个阶段平均耗时，存放在x中
    for row in x.items():
        t,v = row 
#        print 't',t,v
        for aa in v.keys():
#            print 'aa',aa,v[aa],y[t][aa]
            if aa!= 'kfzx' and y[t][aa] != 0 and aa!= 'CS':
                if v[aa] !='' and y[t][aa] != '':
                    v[aa]=round(float(v[aa])/y[t][aa],0)
                if tbl_y[str(t)][aa]>0 and stage_tbl.has_key(aa):
#                    v[stage_tbl[aa]] = str(round(float(y[t][aa])/total_avg_y[aa],4)*100)+'%'
                    v[stage_tbl[aa]] = str(round(float(y[t][aa])/tbl_y[str(t)][aa],4)*100)+'%'
            elif aa== 'CS' and cs_count.has_key(t) and cs_count[t]!=0:
                v[aa]=round(float(z[t][aa])/cs_count[t],0)
                if tbl_y[str(t)][aa]>0:
                    v[stage_tbl[aa]] = str(round(float(cs_count[t])/tbl_y[str(t)][aa],4)*100)+'%'
                    

    #提取需要展现的结果数值，放置于data中
    for row in x.items():
        t,v = row
        data.append(v)
    for aa in total_avg_x.keys():
        if aa!= 'kfzx' and total_avg_y[aa] != 0 and total_avg_x[aa] !='' and total_avg_y[aa] !='':
            total_avg_x[aa] = round(total_avg_x[aa]/total_avg_y[aa],0)
        if total_avg_y[aa]>0 and task_total_all>0 and stage_tbl.has_key(aa) and task_total_all.has_key(aa):
            total_avg_x[stage_tbl[aa]] = str(round(float(total_avg_y[aa])/task_total_all[aa],4)*100)+'%'
#        if total_avg_y[aa]>0 and stage_tbl.has_key(aa):
#            v[stage_tbl[aa]] = round(float(y[t][aa])/total_avg_y[aa],0)
        
    data.append(total_avg_x)
    data.sort(lambda p, q: cmp(p['kfzx'], q['kfzx']))

    pageno = int(request.values.get('page', 1)) - 1
    rows_per_page = int(request.values.get('rows', settings.get_var('PARA/ROWS_PER_PAGE', 10)))
    def i_kfzx(value, obj):
        name = ''
        if value == 99994:
            name = u'总均值'
        else:      
            rs_sel = select([Kfzx.c.name],and_(Kfzx.c.id == value))
            rs_sel_r = rs_sel.execute()
            for row in rs_sel_r:
                name, = row
        #res_tx = tx.get(tx.c.id == value)
        return name
    fields_convert_map={'kfzx':i_kfzx}
        
    view =  SimpleListView(header, data,
            pagination=True, fields_convert_map=fields_convert_map, pageno=pageno,#total_fields=total_fields,
            id='req_table_detail', rows_per_page=rows_per_page)
    
    
    if 'download' in request.GET:
#        fields_convert_map={'kfzx':i_kfzx}
        return view.download('manage_task_avgtime.xls', action='download', 
            timeout=0,fields_convert_map=fields_convert_map)
    elif 'data' in request.values:
#        data = {'query_form':query_view.form}
#        result = view.run(head=False, body=True)
#        return json(result)
        return json(view.json())
    else:
        result = view.run(head=True, body=True)
        result['table'] = view
        result.update({'query_form':query.form})
        return result
    
#管理类报表----任务所属阶段平均耗时
@expose('/charts/manage/requirements/stage/avgtime')
def charts_manage_requirements_stage_avgtime():

    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from uliweb.utils.common import get_var
    from uliweb.core.html import Tag
    from charts.views import charts_manage_requirements
    from uliweb import settings
    from uliweb.utils.common import get_choice
    from query import create_manage_requirement_avgtime_query
    import copy
    
    query = create_manage_requirement_avgtime_query(url_for(charts_manage_requirements_stage_avgtime))
    c = query.run()
    pageno = int(request.GET.get('pageno', 0))
    

    Requirement_details = get_model('requirementdetails')    
    Tx = get_model('tx')
#    Kfzx = get_model('kfzx')
    #   sys = get_model('system')
    Req = get_model('requirement')
    condition =None
    condition = (Requirement_details.c.stage != '18') & condition
    condition = (Requirement_details.c.stage != '19') & condition
    condition = (Requirement_details.c.stage != 'CL') & condition
    mode = c['mode']
    condition = (Requirement_details.c.mode == c['mode']) & condition
    
    if c.get('begin_date'):
        condition = (Requirement_details.c.begin_date >= c['begin_date']) & condition
    if c.get('end_date'):
        condition = (Requirement_details.c.begin_date <= c['end_date']) & condition

    sql_total = select([
                      func.count(Req.c.id)],and_(Req.c.stage != '18',
                                                 Req.c.stage != '19',
                                                 Req.c.stage != 'CL')
                    )
    rs_sql_total = sql_total.execute()
#    total_tx_num = {}

    total_num = 0
    for row in rs_sql_total:
        total_num, = row
    sql_task = select([func.sum(func.datediff(Requirement_details.c.end_date,Requirement_details.c.begin_date) + 1),
                    Requirement_details.c.tx,
                    Requirement_details.c.stage,
                    func.count(Requirement_details.c.id)],and_(condition,Requirement_details.c.stage != '1A')
                    ).group_by(Requirement_details.c.tx,Requirement_details.c.stage)             
    
#    print sql_task
    rs_sql_task = sql_task.execute()
    data = []
    header = []
    
#    if mode == '01':
    header.append({'name':'tx','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/条线', 'width':70})

#    else:
#        header.append({'name':'kfzx','verbose_name':'中心', 'width':70})
#    header.append({'name':'wks','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/未开始/填报率', 'width':70})
#    header.append({'name':'00','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/未开始/平均耗时', 'width':70})
    if mode == '01':
        header.append({'name':'ky','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/需求可研/填报率', 'width':40})
        header.append({'name':'11','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/需求可研/平均耗时', 'width':50})
        header.append({'name':'jgjy','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/架构建议/填报率', 'width':40})
        header.append({'name':'12','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/架构建议/平均耗时', 'width':50})
        header.append({'name':'fatj','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/方案提交/填报率', 'width':40})
        header.append({'name':'13','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/方案提交/平均耗时', 'width':50})
        header.append({'name':'sh','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/ITSS审核/填报率', 'width':40})
        header.append({'name':'14','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/ITSS审核/平均耗时', 'width':50})
        header.append({'name':'gs','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/概算(PMS录入)/填报率', 'width':40})
        header.append({'name':'15','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/概算(PMS录入)/平均耗时', 'width':50})
        header.append({'name':'lxsh','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/立项审核(实施风险评估)/填报率', 'width':70})
        header.append({'name':'16','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/立项审核(实施风险评估)/平均耗时', 'width':70})
        header.append({'name':'bllx','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/办理立项/填报率', 'width':40})
        header.append({'name':'17','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/办理立项/平均耗时', 'width':50})
        header.append({'name':'sstg','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/实施推广/填报率', 'width':40})
        header.append({'name':'18','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/实施推广/平均耗时', 'width':50})
        
        
        #   task_stage_num = {'00':1,'02':2,'03':3,'04':4,'05':5,'06':6,'07':7,'08':8,'09':9,'10':10,'11':11,'FN':12}
        x = {}
        for row in Tx.all().order_by(Tx.c.id):
    #        row = list(row)
            x[str(row.id)] = {'tx':row.id,'00':0,'wks':'','11':0,'ky':'','12':0,'jgjy':'','13':0,'fatj':'',
                              '14':0,'sh':'','15':0,'gs':'','16':0,'lxsh':'','17':0,'bllx':'','18':0,'sstg':'','FN':0,'wc':''}
    
    if mode == '02':
        header.append({'name':'ky','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/需求可研/填报率', 'width':40})
        header.append({'name':'21','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/需求可研/平均耗时', 'width':50})
        header.append({'name':'jgjy','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/架构建议/填报率', 'width':40})
        header.append({'name':'22','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/架构建议/平均耗时', 'width':50})
        header.append({'name':'fabx','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/方案编写/填报率', 'width':40})
        header.append({'name':'23','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/方案编写/平均耗时', 'width':50})
        header.append({'name':'rwsq','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/开发任务申请/填报率', 'width':40})
        header.append({'name':'24','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/开发任务申请/平均耗时', 'width':50})
        header.append({'name':'rwxd','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/等待任务书下达/填报率', 'width':40})
        header.append({'name':'25','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/等待任务书下达/平均耗时', 'width':50})
        header.append({'name':'sstg','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/实施推广/填报率', 'width':40})
        header.append({'name':'26','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/实施推广/平均耗时', 'width':50})
                
        #   task_stage_num = {'00':1,'02':2,'03':3,'04':4,'05':5,'06':6,'07':7,'08':8,'09':9,'10':10,'11':11,'FN':12}
        x = {}
        for row in Tx.all().order_by(Tx.c.id):
    #        row = list(row)
            x[str(row.id)] = {'tx':row.id,'00':0,'wks':'','21':0,'ky':'','22':0,'jgjy':'',
                               '23':0,'fabx':'','24':0,'rwsq':'','25':0,'rwxd':'','26':0,'sstg':'','FN':0,'wc':''}
    if mode == '03' or mode == '09':
        header.append({'name':'jxz','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/进行中/填报率', 'width':70})
        header.append({'name':'31','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/进行中/平均耗时', 'width':70})
        #   task_stage_num = {'00':1,'02':2,'03':3,'04':4,'05':5,'06':6,'07':7,'08':8,'09':9,'10':10,'11':11,'FN':12}
        x = {}
        for row in Tx.all().order_by(Tx.c.id):
    #        row = list(row)
            x[str(row.id)] = {'tx':row.id,'00':0,'wks':'','31':0,'jxz':'','FN':0,'wc':''}
    header.append({'name':'wc','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/完成/填报率', 'width':40})
    header.append({'name':'FN','verbose_name':'准开发需求各阶段平均耗时统计表(平均耗时单位：天)/完成/平均耗时', 'width':50})
    jd={'00':'wks','11':'ky','12':'jgjy','13':'fatj',
        '14':'sh','15':'gs','16':'lxsh','17':'bllx','18':'sstg','FN':'wc',
        '21':'ky','22':'jgjy','23':'fabx','24':'rwsq','25':'rwxd',
        '26':'sstg','31':'jxz'}
        
    total_stage_num ={}
    total_tx_num = {}
    
    total_stage_num =copy.copy(x['1'])
    total_tx_num = copy.copy(x['1'])
    total_y = copy.copy(x['1'])
#    for row in x['1'].items():
#        total_stage_num.
#        total_tx_num.append(row)

    for row in rs_sql_task:
        num,tx,stage,tx_num = row
        total_stage_num[stage] +=int(tx_num)
        total_tx_num[stage] +=int(num)
    #       x = [0,0,0,0,0,0,0,0,0,0,0,0,0]
    #       x[0] = kfzx
        x[str(tx)][stage] = round(num/tx_num,0)
        x[str(tx)][jd[stage]] = str(round(float(tx_num)/total_num,4)*100)+'%'
        
    for row in x.items():
        t,v = row
        data.append(v)
    
#    print 'dddddddddd',data
#    print 'total_stage_num',total_stage_num
#    print 'total_tx_num',total_tx_num
#    print 'total_num',total_num
    
    for row in jd.keys():
#        a,b=row
        if total_y.has_key(row):
            if total_stage_num[row] !=0:
                total_y[row] = round(total_tx_num[row]/total_stage_num[row],0)
            if total_stage_num[row] !=0 and total_num !=0:
                total_y[jd[row]] = str(round(total_stage_num[str(row)]/float(total_num),4)*100)+'%'
#                print '***************',row,total_stage_num[row],total_num
    total_y['tx'] = 99994
    data.append(total_y)
#    print 'cccccccccccc',total_y
#    print 'ffffffffffffff',data
    data.sort(lambda p, q: cmp(p['tx'], q['tx']))
    
    pageno = int(request.values.get('page', 1)) - 1
    rows_per_page = int(request.values.get('rows', settings.get_var('PARA/ROWS_PER_PAGE', 20)))
    def i_tx(value, obj):
        if value == 99994:
            name = u'总均值'
        else:            
            rs_sel = select([Tx.c.name],and_(Tx.c.id == value))    
            rs_sel_r = rs_sel.execute()
            name = ''
            for row in rs_sel_r:
                name, = row            
        return name
#       return value
    fields_convert_map={'tx':i_tx}
#    fields_convert_map={'tx':i_tx}
    view =  SimpleListView(header, data,
            pagination=True, fields_convert_map=fields_convert_map, pageno=pageno,
            id='req_table_detail', rows_per_page=rows_per_page)
    
    
    if 'download' in request.GET:
#        fields_convert_map={'kfzx':i_kfzx}
        return view.download('manage_task_avgtime.xls', action='download', 
            timeout=0,fields_convert_map=fields_convert_map)
    elif 'data' in request.values:
#        data = {'query_form':query_view.form}
#        result = view.run(head=False, body=True)
#        return json(result)
        return json(view.json())
    else:
        result = view.run(head=True, body=True)
        result['table'] = view
        result.update({'query_form':query.form})
        return result


def get_work_zx_list_view(c):
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from uliweb.utils.common import get_var
    from uliweb.core.html import Tag
    from charts.views import charts_manage_requirements
    from uliweb import settings
    from uliweb.utils.common import get_choice

    pageno = int(request.GET.get('pageno', 0))
    
    Task_report = get_model('tasks_develop_report_detail')    
    kfzx = get_model('kfzx')
    sys = get_model('system')
    begin_date = None
    end_date = None
    
    query = None
    con_task = None
            
    report_type = '1'
    if c.get('type'):
        report_type = str(c['type'])
        con_task = (Task_report.c.report_type == (c['type'])) & con_task
    if c.get('begin_date'):
        begin_date = c.get('begin_date')
        con_task = (Task_report.c.report_date >= c['begin_date']) & con_task
    if c.get('end_date'):
        end_date = c.get('end_date')
        con_task = (Task_report.c.report_date <= c['end_date']) & con_task
    
    sql_task = select([Task_report.c.kfzx,
                func.sum(Task_report.c.milestone_delay_num),
                func.sum(Task_report.c.milestone_change_num),
                func.sum(Task_report.c.milestone_total_num),],                
                con_task).group_by(Task_report.c.kfzx)
    sql_task_rs = list(sql_task.execute())
    
    data=[]

    for row in kfzx.filter(kfzx.c.type == '1'):
        x = [0]*6
        x[0] = row.id     
        for r in sql_task_rs:
            #任务
            if r[0]==row.id:
                x[1]=round(r[1],2)
                x[2]=round(r[2],2)
                x[3]=round(r[3],2)
                if r[3] != 0:
#                    x[4]=1-r[1]/r[3]
#                    x[5]=1-r[2]/r[3]
                    x[4]=r[1]/r[3]
                    x[5]=r[2]/r[3]
                else:
#                    x[4]=1
#                    x[5]=1
                    x[4]=0
                    x[5]=0
                    
                    
        data.append(x)
    

    #转换开发任务相关链接,key为header字段名, value为convert函数参数
    fields_convert_map = {}
    xx = {
        'task_milestone_delay_num':{'milestone_delay_num':1},
        'task_milestone_change_num':{'milestone_change_num':1},
        'task_milestone_total_num':{'milestone_total_num':1}
        }
    for k, v in xx.items():
        def _f(value, obj, v=v):
            v['kfzx'] = obj['zx']
            return str(Tag('a', value, href=url_for(charts_manage_tasks_develop, 
                begin_date=begin_date,end_date=end_date,report_type=report_type,
                **v),target='_blank'))
        fields_convert_map.update({k:_f})
    
    def i_zx(value, obj):
        rs_sel = select([kfzx.c.name],and_(kfzx.c.id == value))
            
        rs_sel_r = rs_sel.execute()
        name = ''
        for row in rs_sel_r:
            name, = row
        #res_tx = tx.get(tx.c.id == value)
        return name
    def task_milestone_delay_rate(value,obj):
        return str(round(value,4)*100)+'%'
    def task_milestone_change_rate(value,obj):
        return str(round(value,4)*100)+'%'
    
    
    fields_convert_map.update({'zx':i_zx,'task_milestone_delay_rate':task_milestone_delay_rate,
        'task_milestone_change_rate':task_milestone_change_rate})
 
    def avg1(v,totals):
        if totals['task_milestone_total_num']==0:
            return '0.0%'
        return str(round(1-float(totals['task_milestone_delay_num'])/float(totals['task_milestone_total_num']),4)*100)+'%'
    def avg2(v,totals):
        if totals['task_milestone_total_num']==0:
            return '0.0%'
        return str(round(1-float(totals['task_milestone_change_num'])/float(totals['task_milestone_total_num']),4)*100)+'%'
    
    total_fields={'total_field_name':'合计', 
        'fields':[
            'task_milestone_delay_num',
            'task_milestone_change_num',
            'task_milestone_total_num',
            {'name':'task_milestone_delay_rate','render':avg1},
            {'name':'task_milestone_change_rate','render':avg2},
            ]}
    
    pageno = int(request.values.get('page', 1)) - 1
    rows_per_page = int(request.values.get('rows', settings.get_var('PARA/ROWS_PER_PAGE', 10)))
    
    view =  SimpleListView(settings.PARA.work_zx_report, data,total_fields=total_fields,
            pagination=True, fields_convert_map=fields_convert_map, pageno=pageno,
            id='req_table_detail', rows_per_page=rows_per_page)

    return view

def create_manage_work_zx_query(url):
    from uliweb.utils.generic import QueryView
    from uliweb.utils.generic import ReferenceSelectField
    from uliweb.form import DateField,StringField
    from plugs.ui.jquery.jquery.widgets import DatePicker
    from uliweb import settings, request
    from uliweb.form import SelectField
    from uliweb.orm import get_model
    from datetime import date
    
    query = None
    t_date = date.today()
    def_date = t_date.replace(month=1,day=1)
    
    begin_date = DateField('开始日期', build=DatePicker,default=def_date)
    end_date = DateField('截止日期', build=DatePicker,default=t_date)
    type = SelectField('报表类型', default='1', choices=settings.get_var('PARA/REPORT_TYPE'))


    fields = (('type',type), ('begin_date', begin_date), ('end_date', end_date))
    layout = [('type','begin_date', 'end_date'),]
    query = QueryView('manage_requirement_week', ok_url=url, fields=fields, layout=layout)
    return query

#管理类报表----中心考核
@expose('/charts/manage/zx/total')
def charts_manage_zx_total():
    query = create_manage_work_zx_query(url_for(charts_manage_zx_total))
    condition = query.run()
    view = get_work_zx_list_view(condition)
    
  
    if 'download' in request.GET:
        fields_convert_map = {}
        return view.download('charts_manage_zx_total.xls', action='download', 
            timeout=0,fields_convert_map=fields_convert_map)
    elif 'data' in request.values:
#        result = view.run(head=False, body=True)
#        return json(result)
        return json(view.json())
    else:
        result = view.run(head=True, body=True)
        result['table'] = view
        result.update({'query_form':query.form})
        return result

#管理类报表-系统自主研发月报
@expose('/charts/manage/selfdev/sys/month')
def charts_manage_selfdev_sys_month():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from query import create_manage_resource_sys_month_query
    from uliweb.core.html import Tag
    from decimal import Decimal
    from datetime import date
        
    query = create_manage_resource_sys_month_query(url_for(charts_manage_resource_sys_month))
    c = query.run()
    
    sel_yearmonth = None
    sel_yearmonth_begin = None
    if c.get('year') and c.get('month'):
        year_list = dict(settings.PARA.YEAR_LIST)
        month_list = dict(settings.PARA.MONTH_LIST)
        sel_yearmonth = int(year_list[int(c['year'])])*100+int(month_list[int(c['month'])])
        sel_yearmonth_begin = int(year_list[int(c['year'])])*100 + 1
    
    #取得报表日期
    if int(month_list[int(c['month'])])+1 == 13:
        rep_date = date(int(year_list[int(c['year'])+1]),1,1)
    else:
        rep_date = date(int(year_list[int(c['year'])]),int(month_list[int(c['month'])])+1,1)
    rep_date_begin = date(int(year_list[int(c['year'])]),1,1)
    rep_date_end = date(int(year_list[int(c['year'])]),12,31)
    
    days = float(get_month_days(str(sel_yearmonth)))
    
    sys = get_model('system')
    zx = get_model('kfzx')
    task = get_model('tasks_details')
    task_dev = get_model('tasks_develop')
    selfdev = get_model('selfdev')

    #生成表头
    header = []
    header.append({'name':'sys_id','verbose_name':u'系统编号', 'width':70})
    header.append({'name':'name','verbose_name':u'系统名称', 'width':200})
    header.append({'name':'type','verbose_name':u'系统属性', 'width':70})
    header.append({'name':'kfzx','verbose_name':u'所属中心', 'width':70})        
    header.append({'name':'zx_workload','verbose_name':u'已投入资源(参与自主率考核部分)/中心工作量', 'width':100})
    header.append({'name':'gs_workload','verbose_name':u'已投入资源(参与自主率考核部分)/公司工作量', 'width':100})
    header.append({'name':'nonselfdev_zx_workload','verbose_name':u'已投入资源 1/中心工作量(参与考核)', 'width':110})
    header.append({'name':'nonselfdev_gs_workload','verbose_name':u'已投入资源 1/公司工作量(不参与考核)', 'width':110})
    header.append({'name':'selfdev_rate','verbose_name':u'系统考核用自主率/考核用自主率', 'width':100})
    header.append({'name':'selfdev_divided_rate','verbose_name':u'系统考核用自主率/与上年底比较', 'width':100})
    header.append({'name':'selfdev_divided_rate_thisyear','verbose_name':u'系统考核用自主率/与本年底目标比较', 'width':100})
    header.append({'name':'total_zx_workload','verbose_name':u'全部已投入资源/中心工作量', 'width':100})
    header.append({'name':'total_gs_workload','verbose_name':u'全部已投入资源/公司工作量', 'width':100})
    header.append({'name':'total_selfdev_rate','verbose_name':u'全部已投入资源/自主率', 'width':100})
    
    header.append({'name':'zx_workload_estimate','verbose_name':u'全年资源预计自主率/中心工作量', 'width':100})
    header.append({'name':'gs_workload_estimate','verbose_name':u'全年资源预计自主率/公司工作量', 'width':100})
    header.append({'name':'selfdev_rate_estimate','verbose_name':u'全年资源预计自主率/自主率', 'width':100})
    header.append({'name':'selfdev_divided_rate_estimate_lastyear','verbose_name':u'全年资源预计自主率/与上年底比较', 'width':100})
    header.append({'name':'selfdev_divided_rate_estimate','verbose_name':u'全年资源预计自主率/与本年底目标比较', 'width':100})
    
    #新増
    header.append({'name':'selfdev_if_resource_input','verbose_name':u'是否XX系统-统计用数据/有资源投入', 'width':100})
    header.append({'name':'selfdev_if_join','verbose_name':u'是否XX系统-统计用数据/参与系统自主率考核', 'width':100})
    header.append({'name':'selfdev_if_has_extra','verbose_name':u'是否XX系统-统计用数据/有不参与考核的资源', 'width':100})
    header.append({'name':'selfdev_if_predict_total','verbose_name':u'是否XX系统-统计用数据/全年资源预计(全部)', 'width':100})
    
    condition = None
    if c.get('system'):
        condition = (sys.c.id == (int(c['system']))) & condition
    if c.get('zx'):
        condition = (sys.c.kfzx == (int(c['zx']))) & condition
    
    if request.user.department == '2':
        if request.user.is_gm:
            condition = (sys.c.kfzx== request.user._kfzx_)  & condition
        else:
            ids = request.user.users_systems.ids()
            condition = (sys.c.id.in_(ids)) & condition
    #取非虚拟系统
    condition = (sys.c.sys_type!='2') & condition
    #滤掉香港支持中心系统
    condition = (sys.c.kfzx != 8) & condition

    #初始化结构
    data = []
    if request.user.department != '3':    
        for k in sys.filter(condition).all().order_by(sys.c.sys_type,sys.c.kfzx):
            if k.sys_type == '5':
                continue                
            j = {}   
            j['name']=k.name
            j['type']=unicode(dict(settings.PARA.SYSTEM_TYPE)[k.sys_type],'utf-8')
            j['kfzx']=k.kfzx.name                               
            j['id']=k.id  
            j['sys_id']=k.sys_id
            j['zx_workload']=0
            j['gs_workload']=0
            j['selfdev_rate']=0
            j['nonselfdev_zx_workload']=0
            j['nonselfdev_gs_workload']=0
            j['total_zx_workload']=0
            j['total_gs_workload']=0
            j['total_selfdev_rate']=0
            
            j['selfdev_divided_rate']=0
            j['zx_workload_estimate']=0
            j['gs_workload_estimate']=0
            j['selfdev_rate_estimate']=0
            j['selfdev_divided_rate_thisyear']=0
            j['selfdev_divided_rate_estimate_lastyear']=0
            j['selfdev_divided_rate_estimate']=0
            
            #新増
            j['selfdev_if_resource_input'] = 0
            j['selfdev_if_join'] = 0
            j['selfdev_if_has_extra'] = 0
            j['selfdev_if_predict_total'] = 0
            
            data.append(j)
    if request.user.department != '2' or request.user.department != '3':
        total={}
        total['id']=u'总计'
        total['sys_id']=u'总计'
        total['name']=u'--'
        total['type']=u'--'
        total['kfzx']=u'--'                
        total['zx_workload']=0
        total['gs_workload']=0
        total['selfdev_rate']=0
        total['nonselfdev_zx_workload']=0
        total['nonselfdev_gs_workload']=0
        total['total_zx_workload']=0
        total['total_gs_workload']=0
        total['total_selfdev_rate']=0
        
        total['selfdev_divided_rate']=0
        total['selfdev_divided_rate_thisyear']=0
        total['zx_workload_estimate']=0
        total['gs_workload_estimate']=0
        total['selfdev_rate_estimate']=0
        total['selfdev_divided_rate_estimate_lastyear']=0
        total['selfdev_divided_rate_estimate']=0
        
        #新増
        total['selfdev_if_resource_input'] = 0
        total['selfdev_if_join'] = 0
        total['selfdev_if_has_extra'] = 0
        total['selfdev_if_predict_total'] = 0
        
        data.append(total)
    
    #生成数据
    #自主研发工作量只统计('01', '项目任务'), ('02', '子项目任务'), ('03', '非项目任务'),('04', '无任务书开发任务'), ('05', '可研')
    condition = (task.c.task_type.in_(['01','02','03','04','05'])) & condition
    
    condition = (task.c.task_sys == sys.c.id) & condition
    condition = (task_dev.c.task_id == task.c.task_id) & condition
    condition_workload = ((sys.c.selfdev_if == True) & (task_dev.c.selfdev_if == True))& condition
    condition_workload_nonselfdev = ((sys.c.selfdev_if == False) | ((sys.c.selfdev_if == True) & (task_dev.c.selfdev_if == False)))& condition
    #计入自主研发率本年度已投入工作量
    sql = select([task.c.task_sys,
            func.sum(task.c.amount_inner_input),
            func.sum(task.c.amount_outer_input),
            ],and_(task.c.year_month<=sel_yearmonth,
            task.c.year_month>=sel_yearmonth_begin,
            condition_workload))\
            .group_by(task.c.task_sys)
    #不计入自主研发率本年度已投入工作量
    sql_nonselfdev = select([task.c.task_sys,
            func.sum(task.c.amount_inner_input),
            func.sum(task.c.amount_outer_input),
            ],and_(task.c.year_month<=sel_yearmonth,
            task.c.year_month>=sel_yearmonth_begin,
            condition_workload_nonselfdev))\
            .group_by(task.c.task_sys)
    
    #本年度有工作量的任务
    sql_task = select([task_dev.c.task_sys,
            task_dev.c.task_id,
            task_dev.c.amount_inner,
            task_dev.c.amount_outer,
            task_dev.c.begin_time,
            task_dev.c.end_time],
            and_(task_dev.c.end_time>rep_date_begin,
            ))
    
    id_list = []  #本年度有工作量的任务id集合
    rs_list = {}  #本年度有工作量的任务相关信息集合
    sql_task_rs = sql_task.execute()
    for row in sql_task_rs:
        task_sys,task_id,amount_inner,amount_outer,begin_time,end_time = row
        id_list.append(task_id)
        x = dict(row)
        x.update({'task_id':task_id,'amount_inner_input':0,'amount_outer_input':0,'stage':'00'})
        rs_list[task_id]=x
    #本年度有工作量的任务截止至报表日期累计已投入工作量
    sql_task_input = select([task.c.task_id,
            func.sum(task.c.amount_inner_input).label('amount_inner_input'),
            func.sum(task.c.amount_outer_input).label('amount_outer_input'),
            ],and_(
            task.c.task_id == task_dev.c.task_id,
            task_dev.c.end_time>rep_date_begin,
            #截止至报表日期的工作量
            task.c.year_month<=sel_yearmonth,
            ))\
            .group_by(task.c.task_id)

    sql_task_input_rs = sql_task_input.execute()
    for row in sql_task_input_rs:
        task_id,amount_inner_input,amount_outer_input = row
        if rs_list.has_key(task_id):
            rs_list[task_id].update(dict(row))
    
    #本年度有工作量的任务在报表日期所处阶段
    sql_task_stage = select([task.c.task_id,
            task.c.stage,
            ],and_(
            task.c.task_id == task_dev.c.task_id,
            task_dev.c.end_time>rep_date_begin,
            #截止至报表日期
            task.c.year_month<=sel_yearmonth,
            ))\
            .group_by(task.c.task_id).having(func.max(task.c.end_time))
    
    sql_task_stage_rs = sql_task_stage.execute()
    for row in sql_task_stage_rs:
        task_id,stage = row
        if rs_list.has_key(task_id):
            rs_list[task_id].update(dict(row))
    
    #计算每个本年度有工作量任务的预计工作量
    for k,v in rs_list.items():
        #计算中心、公司预计投入
        x = 0
        y = 0
        #如果已投入工作量大于预计工作量，或者任务已经完成，则预计投入为0
        if (v['amount_inner']<=v['amount_inner_input']) or (v['stage']=='FN'):
            x=0
        if (v['amount_outer']<=v['amount_outer_input']) or (v['stage']=='FN'):            
            y=0
        #如果结束日期小于报表日期或者结束日期小于本年底，那么预计工作量为预计投入减去已投入
        elif (v['end_time']<=rep_date) or (v['end_time']<=rep_date_end):
            x = v['amount_inner']-v['amount_inner_input']
            y = v['amount_outer']-v['amount_outer_input']
        else:
            x= (v['amount_inner']-v['amount_inner_input'])*(rep_date_end-rep_date).days/(v['end_time']-rep_date).days
            y= (v['amount_outer']-v['amount_outer_input'])*(rep_date_end-rep_date).days/(v['end_time']-rep_date).days
        
        #加入结果列表
        #update 2011-10-11 如果中心和公司资源为负，则置为0
        rs_list[k].update({'amount_inner_estimate':max(x, 0),'amount_outer_estimate':max(y, 0)})
        
    sys_list = []
    for i in data:
        sys_list.append(i['id'])
    #统计计入自主研发率的工作量统计    
    if request.user.department != '3':
        sql_rs = sql.execute()
        for row in sql_rs:
            system,zx_sum,gs_sum = row
            for i in data:
                if not system:
                    continue
                if i['id']==system:
                    i['zx_workload']+=zx_sum
                    i['gs_workload']+=gs_sum
                    i['zx_workload_estimate']+=zx_sum
                    i['gs_workload_estimate']+=gs_sum
                if i['id']==u'总计':
                    i['zx_workload']+=zx_sum
                    i['gs_workload']+=gs_sum
                    i['zx_workload_estimate']+=zx_sum
                    i['gs_workload_estimate']+=gs_sum
    
        for k,v in rs_list.items():
            for i in data:
                if i['id']==v['task_sys']:
                    i['zx_workload_estimate']+=v['amount_inner_estimate']
                    i['gs_workload_estimate']+=v['amount_outer_estimate']
                if (i['id']==u'总计') and (v['task_sys'] in sys_list):
                    i['zx_workload_estimate']+=v['amount_inner_estimate']
                    i['gs_workload_estimate']+=v['amount_outer_estimate']
    
    #统计不计入自主研发率的工作量统计
    if request.user.department != '3':
        sql_rs = sql_nonselfdev.execute()
        for row in sql_rs:
            system,zx_sum,gs_sum = row
            for i in data:
                if not system:
                    continue
                if i['id']==system:
                    i['nonselfdev_zx_workload']+=zx_sum
                    i['nonselfdev_gs_workload']+=gs_sum
#                    i['zx_workload_estimate']+=zx_sum
#                    i['gs_workload_estimate']+=gs_sum
                if i['id']==u'总计':
                    i['nonselfdev_zx_workload']+=zx_sum
                    i['nonselfdev_gs_workload']+=gs_sum
#                    i['zx_workload_estimate']+=zx_sum
#                    i['gs_workload_estimate']+=gs_sum
    
 
    #数据格式转换
    for i in data:
        if i['zx_workload']+i['gs_workload']+i['nonselfdev_zx_workload']>0:
#            i['selfdev_rate'] = i['zx_workload']/(i['zx_workload']+i['gs_workload'])
            i['selfdev_rate'] = (i['zx_workload']+i['nonselfdev_zx_workload'])/(i['zx_workload']+i['gs_workload']+i['nonselfdev_zx_workload'])
       
        i['total_zx_workload'] = i['zx_workload'] + i['nonselfdev_zx_workload']
        i['total_gs_workload'] = i['gs_workload'] + i['nonselfdev_gs_workload']
        i['zx_workload_estimate'] += i['nonselfdev_zx_workload']
        i['gs_workload_estimate'] += i['nonselfdev_gs_workload']
        if i['total_zx_workload']+i['total_gs_workload']>0:
            i['total_selfdev_rate'] = i['total_zx_workload']/(i['total_zx_workload']+i['total_gs_workload'])
        
        
        if i['zx_workload_estimate']+i['gs_workload_estimate']>0:
            i['selfdev_rate_estimate'] = i['zx_workload_estimate']/(i['zx_workload_estimate']+i['gs_workload_estimate'])
        #与上年底比较和与本年底目标比较计算
        if i['id']==u'总计':
            i['selfdev_divided_rate'] = None
            i['selfdev_divided_rate_thisyear'] = None
            i['selfdev_divided_rate_estimate'] = None
            i['selfdev_divided_rate_estimate_lastyear'] = None
        else:
            divided_obj = selfdev.get(selfdev.c.system == i['id'])
            if divided_obj:
                i['selfdev_divided_rate'] = i['selfdev_rate'] - (divided_obj.independent_lastyear/100)
                i['selfdev_divided_rate_thisyear'] = i['selfdev_rate'] - (divided_obj.independent_thisyear/100)
                i['selfdev_divided_rate_estimate'] = i['selfdev_rate_estimate'] - (divided_obj.independent_thisyear/100)
                i['selfdev_divided_rate_estimate_lastyear'] = i['selfdev_rate_estimate'] - (divided_obj.independent_lastyear/100)
            else:
                i['selfdev_divided_rate'] = None
                i['selfdev_divided_rate_thisyear'] = None
                i['selfdev_divided_rate_estimate'] = None
                i['selfdev_divided_rate_estimate_lastyear'] = None
#                print '+++++++++',i
            if i['selfdev_divided_rate']:
                i['selfdev_divided_rate'] = str(round(i['selfdev_divided_rate']*100,1))+'%'
            if i['selfdev_divided_rate_thisyear']:
                i['selfdev_divided_rate_thisyear'] = str(round(i['selfdev_divided_rate_thisyear']*100,1))+'%'            
            if i['selfdev_divided_rate_estimate']:
                i['selfdev_divided_rate_estimate'] = str(round(i['selfdev_divided_rate_estimate']*100,1))+'%'
            if i['selfdev_divided_rate_estimate_lastyear']:
                i['selfdev_divided_rate_estimate_lastyear'] = str(round(i['selfdev_divided_rate_estimate_lastyear']*100,1))+'%'
            i['selfdev_if_resource_input'] = int(any([i['zx_workload'], i['gs_workload'], i['nonselfdev_zx_workload'], i['nonselfdev_gs_workload']]))
            if i['zx_workload'] + i['gs_workload'] > 0:
                i['selfdev_if_join'] = 1
            if i['nonselfdev_gs_workload'] > 0:
                i['selfdev_if_has_extra'] = 1
            i['selfdev_if_predict_total'] = str(round(sum([i['zx_workload_estimate'], i['gs_workload_estimate']]),1))
            
        if i['total_selfdev_rate']:
                i['total_selfdev_rate'] = str(round(i['total_selfdev_rate']*100,1))+'%'
        i['zx_workload']=str(round(i['zx_workload'],1))
        i['nonselfdev_zx_workload']=str(round(i['nonselfdev_zx_workload'],1))
        i['total_zx_workload']=str(round(i['total_zx_workload'],1))
        i['gs_workload']=str(round(i['gs_workload'],1))
        i['nonselfdev_gs_workload']=str(round(i['nonselfdev_gs_workload'],1))
        i['total_gs_workload']=str(round(i['total_gs_workload'],1))
        i['zx_workload_estimate']=str(round(i['zx_workload_estimate'],1))
        i['gs_workload_estimate']=str(round(i['gs_workload_estimate'],1))
        i['selfdev_rate']=str(round(i['selfdev_rate']*100,1))+'%'
#        i['total_selfdev_rate']=str(round(i['total_selfdev_rate']*100,1))+'%'
        i['selfdev_rate_estimate']=str(round(i['selfdev_rate_estimate']*100,1))+'%'
        

    pageno = int(request.values.get('page', 1)) - 1
    rows_per_page = int(request.values.get('rows', settings.get_var('PARA/ROWS_PER_PAGE', 10)))
    
    #展示        
    view =  SimpleListView(header, data, id='req_count_table',table_width=True,
        pagination=True, pageno=pageno, rows_per_page=rows_per_page)
    
    if 'download' in request.GET:
        fields_convert_map = {}
        return view.download('resource_sys_month.xls', action='download', timeout=0)
    elif 'data' in request.values:
        return json(view.json())
    else:
        result = view.run(head=True, body=True)
        result['table'] = view
        result.update({'query_form':query.form})
        return result

#管理类报表-开发任务测试工作量指标表
#@expose('/charts/manage/develop/test/workload')
#def charts_manage_develop_test_workload():
#    from uliweb.orm import get_model
#    from uliweb.utils.generic import SimpleListView
#    from uliweb import request
#    from query import create_develop_test_query
#    from uliweb.core.html import Tag
#    from decimal import Decimal
#    from datetime import date
#   
#        
#    task = get_model('tasks_details')
#    task_dev = get_model('tasks_develop')
#        
#    query = create_develop_test_query(url_for(charts_manage_develop_test_workload))
#    c = query.run()
#    
#    condition = None
#    if c.get('task_id'):
#        condition = (task_dev.c.task_id.like('%'+c['task_id']+'%')) & condition
#    if c.get('requirement'):
#        condition = (task_dev.c.requirement == int(c['requirement'])) & condition
#    if c.get('assignment'):
#        condition = (task_dev.c.assignment == int(c['assignment'])) & condition
#    if c.get('kfzx'):
#        condition = (task_dev.c.kfzx == int(c['kfzx'])) & condition
#    if c.get('task_sys'):
#        condition = (task_dev.c.task_sys == int(c['task_sys'])) & condition
##    if c.get('status'):
##        condition = task_dev.c.status.in_(c['status']) & condition
#    if c.get('name'):
#        condition = (task_dev.c.name.like('%'+c['name']+'%')) & condition
##    if c.get('task_type'):
##        condition = task_dev.c.task_type.in_(c['task_type']) & condition
#    if c.get('stage'):
#        condition = task_dev.c.stage.in_(c['stage']) & condition
#    
#    if c.get('begin_date'):
#        condition = (task_dev.c.version_date >= c['begin_date']) & condition
#    if c.get('end_date'):
#        condition = (task_dev.c.version_date <= c['end_date']) & condition
#    
#    if request.GET.get('header_id'):
#        header_id = int(request.GET['header_id'])
#        condition = or_((task_dev.c.id == header_id), (task_dev.c.version_headerId == header_id))
#    
#
#    #生成表头
#    header = []
#    header.append({'name':'task_id','verbose_name':u'任务编号', 'width':70})
#    header.append({'name':'version_headerId','verbose_name':u'主任务ID', 'width':200})
#    header.append({'name':'name','verbose_name':u'开发任务名称', 'width':70})
#    header.append({'name':'kfzx','verbose_name':u'开发中心', 'width':70})        
#    header.append({'name':'task_sys','verbose_name':u'所属系统', 'width':100})
#    header.append({'name':'sub_sys','verbose_name':u'子系统', 'width':100})
#    header.append({'name':'mode','verbose_name':u'项目类型', 'width':100})
#    header.append({'name':'requirement','verbose_name':u'所属准开发需求', 'width':100})
#    header.append({'name':'stage','verbose_name':u'所处阶段', 'width':100})
#    header.append({'name':'begin_time','verbose_name':u'任务开始时间', 'width':100})
#    header.append({'name':'end_time','verbose_name':u'任务结束时间', 'width':100})
#    header.append({'name':'version_date','verbose_name':u'投产时间', 'width':100})
#    header.append({'name':'develop_total_workload','verbose_name':u'开发工作量/总投入', 'width':100})
#    header.append({'name':'develop_zx_workload','verbose_name':u'开发工作量/中心投入', 'width':100})
#    header.append({'name':'develop_gs_workload','verbose_name':u'开发工作量/公司投入', 'width':100})
#    header.append({'name':'test_total_workload','verbose_name':u'测试工作量/总投入', 'width':100})
#    header.append({'name':'test_zx_workload','verbose_name':u'测试工作量/中心投入', 'width':100})
#    header.append({'name':'test_gs_workload','verbose_name':u'测试工作量/公司投入', 'width':100})
#    
#    data = []
#    obj_task = task_dev.filter(and_(condition))
#    i = 0
#    if request.user.department == '3':
#        for row in obj_task:
#            sql_dev = select([func.sum(task.c.amount_inner_input),
#                              func.sum(task.c.amount_outer_input)],
#                              and_(task.c.task_id == row.task_id,task.c.stage.in_(['02','03','04','05','06','11','FN'])))
#            sql_test = select([func.sum(task.c.amount_inner_input),
#                              func.sum(task.c.amount_outer_input)],
#                              and_(task.c.task_id == row.task_id,task.c.stage.in_(['07','08','09','10'])))
#            sql_dev_rs = sql_dev.execute()
#            sql_test_rs = sql_test.execute()
#            j = {}
#            for row_dev in sql_dev_rs:
#                develop_zx_workload,develop_gs_workload = row_dev
#                if not develop_zx_workload:
#                    j['develop_zx_workload'] = 0
#                    j['develop_gs_workload'] = 0
#                    j['develop_total_workload'] = 0
#                    break
#                else:
#                    j['develop_zx_workload'] = round(develop_zx_workload,2)
#                    j['develop_gs_workload'] = round(develop_gs_workload,2)
#                    j['develop_total_workload'] = round(develop_gs_workload + develop_zx_workload,2)
#
#            for row_test in sql_test_rs:
#                test_zx_workload,test_gs_workload = row_test
#                if not test_zx_workload:
#                    j['test_zx_workload'] = 0
#                    j['test_gs_workload'] = 0
#                    j['test_total_workload'] = 0                    
#                    break
#                else:
#                    j['test_zx_workload'] = round(test_zx_workload,2)
#                    j['test_gs_workload'] = round(test_gs_workload,2)
#                    j['test_total_workload'] = round(test_gs_workload + test_zx_workload,2)
#            j['task_id'] = row.task_id
#            if row.version_headerId:
#                j['version_headerId'] = row.version_headerId.task_id
#            else:
#                j['version_headerId'] = None
#            j['name'] = row.name
#            if not row.kfzx:
#                j['kfzx'] = ''
#            else:
#                j['kfzx'] = row.kfzx.name
#            j['task_sys'] = row.task_sys.abbr
#            j['sub_sys'] = row.sub_sys
#            j['mode'] = dict(settings.PARA.TASK_TYPE_DEVELOP)[row.task_type]
#            j['requirement'] = row.requirement.req_id + row.requirement.title
#            j['stage'] = dict(settings.PARA.TASK_STAGE)[row.stage]
#            j['begin_time'] = row.begin_time
#            j['end_time'] = row.end_time
#            j['version_date'] = row.version_date
##            print j
#            data.append(j)
##            i+=1
##            if i >2000:
##                break
##    print data    
#    pageno = int(request.values.get('page', 1)) - 1
#    rows_per_page = int(request.values.get('rows', settings.get_var('PARA/ROWS_PER_PAGE', 10)))
#    
#    #展示        
#    view =  SimpleListView(header, data, id='req_count_table',#table_width=True,
#        pagination=True, pageno=pageno, rows_per_page=rows_per_page)
#    
#    if 'download' in request.GET:
#        fields_convert_map = {}
#        return view.download('develop_test_workload.xls', action='download', timeout=0)
#    elif 'data' in request.values:
#        return json(view.json())
#    else:
#        result = view.run(head=True, body=True)
#        result['table'] = view
#        result.update({'query_form':query.form})
#        return result

#管理类报表-开发任务测试工作量指标表
@expose('/charts/manage/develop/test/workload')
def charts_manage_develop_test_workload():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from query import create_develop_test_query
    from uliweb.core.html import Tag
    from decimal import Decimal
    from datetime import date
   
        
    task = get_model('tasks_details')
    task_dev = get_model('tasks_develop')
    Requirement = get_model('requirement')
    Kfzx = get_model('kfzx')
    System = get_model('system')
        
    query = create_develop_test_query(url_for(charts_manage_develop_test_workload))
    c = query.run()
    
    condition = None
    if c.get('task_id'):
        condition = (task_dev.c.task_id.like('%'+c['task_id']+'%')) & condition
    if c.get('requirement'):
        condition = (task_dev.c.requirement == int(c['requirement'])) & condition
    if c.get('assignment'):
        condition = (task_dev.c.assignment == int(c['assignment'])) & condition
    if c.get('kfzx'):
        condition = (task_dev.c.kfzx == int(c['kfzx'])) & condition
    if c.get('task_sys'):
        condition = (task_dev.c.task_sys == int(c['task_sys'])) & condition
#    if c.get('status'):
#        condition = task_dev.c.status.in_(c['status']) & condition
    if c.get('name'):
        condition = (task_dev.c.name.like('%'+c['name']+'%')) & condition
#    if c.get('task_type'):
#        condition = task_dev.c.task_type.in_(c['task_type']) & condition
    if c.get('stage'):
        condition = task_dev.c.stage.in_(c['stage']) & condition
    
    if c.get('begin_date'):
        condition = (task_dev.c.version_date >= c['begin_date']) & condition
    if c.get('end_date'):
        condition = (task_dev.c.version_date <= c['end_date']) & condition
    
    if request.GET.get('header_id'):
        header_id = int(request.GET['header_id'])
        condition = or_((task_dev.c.id == header_id), (task_dev.c.version_headerId == header_id))
    

    #生成表头
    header = []
    header.append({'name':'task_id','verbose_name':u'任务编号', 'width':120})
    header.append({'name':'version_headerId','verbose_name':u'主任务ID', 'width':120})
    header.append({'name':'name','verbose_name':u'开发任务名称', 'width':200})
    header.append({'name':'kfzx','verbose_name':u'开发中心', 'width':70})        
    header.append({'name':'task_sys','verbose_name':u'所属系统', 'width':100})
    header.append({'name':'sub_sys','verbose_name':u'子系统', 'width':100})
    header.append({'name':'mode','verbose_name':u'项目类型', 'width':100})
    header.append({'name':'requirement','verbose_name':u'所属准开发需求', 'width':200})
    header.append({'name':'stage','verbose_name':u'所处阶段', 'width':100})
    header.append({'name':'begin_time','verbose_name':u'任务开始时间', 'width':100})
    header.append({'name':'end_time','verbose_name':u'任务结束时间', 'width':100})
    header.append({'name':'version_date','verbose_name':u'投产时间', 'width':100})
    header.append({'name':'develop_total_workload','verbose_name':u'开发工作量/总投入', 'width':70})
    header.append({'name':'develop_zx_workload','verbose_name':u'开发工作量/中心投入', 'width':70})
    header.append({'name':'develop_gs_workload','verbose_name':u'开发工作量/公司投入', 'width':70})
    header.append({'name':'test_total_workload','verbose_name':u'测试工作量/总投入', 'width':70})
    header.append({'name':'test_zx_workload','verbose_name':u'测试工作量/中心投入', 'width':70})
    header.append({'name':'test_gs_workload','verbose_name':u'测试工作量/公司投入', 'width':70})
    

    total_num = task_dev.filter(and_(condition)).count()    
#    obj_task = task_dev.filter(and_(condition))
    sql_dev = select([task_dev.c.task_id,task_dev.c.version_headerId,
                      task_dev.c.name,Kfzx.c.name,System.c.abbr,
                      task_dev.c.sub_sys,task_dev.c.task_type,
                      Requirement.c.req_id,Requirement.c.title,
                      task_dev.c.stage,task_dev.c.begin_time,
                      task_dev.c.end_time,task_dev.c.version_date,
                      func.sum(task.c.amount_inner_input),
                      func.sum(task.c.amount_outer_input)],
#                    and_(task.c.task_id == task_dev.c.task_id,Requirement.c.id == task_dev.c.requirement,
                    and_(Requirement.c.id == task_dev.c.requirement,
                         Kfzx.c.id == task_dev.c.kfzx,
                         System.c.id == task_dev.c.task_sys,
                         condition),
                    from_obj=[task_dev.table.outerjoin(task.table,and_(task.c.task_id == task_dev.c.task_id,task.c.stage.in_(['02','03','04','05','06','11','FN'])))]).group_by(task_dev.c.task_id).order_by(task_dev.c.id)
#                                                ).outerjoin(Requirement,Requirement.c.id == task_dev.c.requirement
#                                                            ).outerjoin(System,System.c.id == task_dev.c.task_sys
#                                                                        ).outerjoin(Kfzx,Kfzx.c.id == task_dev.c.kfzx)).group_by(task_dev.c.task_id)
#    sql_test = select([task_dev.c.task_id,task_dev.c.version_headerId,
#                      task_dev.c.name,Kfzx.c.name,task_dev.c.abbr,
#                      task_dev.c.sub_sys,task_dev.c.task_type,
#                      Requirement.c.req_id,Requirement.c.title,
#                      task_dev.c.stage,task_dev.c.begin_time,
#                      task_dev.c.end_time,task_dev.c.version_date,
#                      func.sum(task.c.amount_inner_input),
#                      func.sum(task.c.amount_outer_input)],
#                    and_(task.c.task_id == task_dev.c.task_id,Requirement.c.id == task_dev.c.requirement,
#                         task.c.stage.in_(['07','08','09','10']),
#                         Kfzx.c.id = task_dev.c.kfzx,condition))
    sql_test = select([task_dev.c.task_id,
                      func.sum(task.c.amount_inner_input),
                      func.sum(task.c.amount_outer_input)],
#                    and_(task.c.task_id == task_dev.c.task_id,
#                         task.c.stage.in_(['07','08','09','10']),
                         and_(condition),
                    from_obj=[task_dev.table.outerjoin(task.table,and_(task.c.task_id == task_dev.c.task_id,task.c.stage.in_(['07','08','09','10'])))]).group_by(task_dev.c.task_id).order_by(task_dev.c.id)
    if 'data' in request.values:
        pageno = int(request.values.get('page', 1))- 1
        rows = int(request.values.get('rows', 20))
        sql_dev = sql_dev.limit(rows).offset(rows*pageno)
        sql_test = sql_test.limit(rows).offset(rows*pageno)
#    if download in request.values:
    
#    print sql_dev
    sql_dev_rs = sql_dev.execute()
    sql_test_rs = sql_test.execute()
    data1 = {}
    if ((request.user.department == '3') or (request.user.is_superuser)):
        for row in sql_dev_rs:
            task_id,version_headerId,name,kfzx,abbr,sub_sys,task_type,req_id,title,stage,begin_time,end_time,version_date,develop_zx_workload,develop_gs_workload = row
            data1[task_id] = {}
            data1[task_id]['task_id'] = task_id
            if  version_headerId:
                obj_headID = task_dev.get(task_dev.c.id == version_headerId)
                data1[task_id]['version_headerId'] = obj_headID.task_id
            else:
                data1[task_id]['version_headerId'] = None
            data1[task_id]['name'] = name
            if kfzx:
                data1[task_id]['kfzx'] = kfzx
            else:
                data1[task_id]['kfzx'] = ''
            data1[task_id]['task_sys'] = abbr
            data1[task_id]['sub_sys'] = sub_sys
            data1[task_id]['mode'] = unicode(dict(settings.PARA.TASK_TYPE_DEVELOP)[task_type],'utf8')
            data1[task_id]['requirement'] = req_id + title
            data1[task_id]['stage'] = unicode(dict(settings.PARA.TASK_STAGE)[row.stage],'utf8')
            data1[task_id]['begin_time'] = row.begin_time
            data1[task_id]['end_time'] = end_time
            data1[task_id]['version_date'] = version_date
            if develop_zx_workload:
                data1[task_id]['develop_zx_workload'] = round(develop_zx_workload,2)
            else:
                data1[task_id]['develop_zx_workload'] = 0
                develop_zx_workload = 0
            if develop_gs_workload:
                data1[task_id]['develop_gs_workload'] = round(develop_gs_workload,2)
            else:
                data1[task_id]['develop_gs_workload'] = 0
                develop_gs_workload = 0
            data1[task_id]['develop_total_workload'] = round(develop_zx_workload + develop_gs_workload,2)
            data1[task_id]['test_zx_workload'] = 0
            data1[task_id]['test_gs_workload'] = 0
            data1[task_id]['test_total_workload'] = 0
        
        for row in sql_test_rs:
            task_id,develop_zx_workload,develop_gs_workload = row
            if task_id in data1:
                if develop_zx_workload:
                    data1[task_id]['test_zx_workload'] = round(develop_zx_workload,2)
                else:
                    data1[task_id]['test_zx_workload'] = 0
                    develop_zx_workload = 0
                if develop_gs_workload:
                    data1[task_id]['test_gs_workload'] = round(develop_gs_workload,2)
                else:
                    data1[task_id]['test_gs_workload'] = 0
                    develop_gs_workload = 0
                data1[task_id]['test_total_workload'] = round(develop_zx_workload + develop_gs_workload,2)
    data = []            
    for x,y in data1.items():
         data.append(y)           

    pageno = int(request.values.get('page', 1)) - 1
    rows_per_page = int(request.values.get('rows', settings.get_var('PARA/ROWS_PER_PAGE', 10)))
    
    #展示        
    view =  SimpleListView(header, data, id='req_count_table',#table_width=True,
        pagination=True, pageno=pageno,
        rows_per_page=rows_per_page,total = total_num,manual = True)
    
    if 'download' in request.GET:
        fields_convert_map = {}
        return view.download('develop_test_workload.xls', action='download', timeout=0)
    elif 'data' in request.values:
        return json(view.json())
    else:
        result = view.run(head=True, body=True)
        result['table'] = view
        result.update({'query_form':query.form})
        return result
    
#管理类报表-周报填写情况统计表
@expose('/charts/weekreport/integrity/check/report')
def charts_weekreport_integrity_check_report():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from query import create_weekreport_check_query
    from uliweb.core.html import Tag
    from decimal import Decimal
    from datetime import date
    
    WeekReport = get_model('weekreport')
    PlanExecute = get_model('planexecute')
    PlanExecuteHis = get_model('planexecutehis')
    System = get_model('system')
    Kfzx = get_model('kfzx')
    
    query = create_weekreport_check_query(url_for(charts_weekreport_integrity_check_report))
    c = query.run()

    condition = None
    condition_kfzx = None
#    if (request.user.is_gm)and(request.user.department=='2'):
#        c['kfzx'] = request.user._kfzx_
    if c.get('title'):
        condition = (WeekReport.c.title.like('%'+c['title']+'%')) & condition
    if c.get('care_level'):
        condition = (WeekReport.c.care_level == int(c['care_level'])) & condition
    
    if c.get('kfzx'):
        condition_kfzx = (System.c.kfzx == int(c['kfzx'])) & condition_kfzx
    
    if c.get('begin_date'):
        today = c['begin_date']
    else:
        today = date.today()
    
    
    
    first_day = today - datetime.timedelta(days=(today.weekday()+2)%7)
    last_day = first_day + datetime.timedelta(days=6)
#    print '44444444444',today,first_day,last_day
    #生成表头
    header = []
    header.append({'name':'weekreport','verbose_name':u'事项名称', 'width':200})
    header.append({'name':'kfzx_name','verbose_name':u'开发中心', 'width':70})
    header.append({'name':'weekreport_desc','verbose_name':u'周报总体情况', 'width':70})
    header.append({'name':'system_abbr','verbose_name':u'未填报本周完成情况的系统', 'width':300})
    header.append({'name':'kfzx_num','verbose_name':u'未填报系统数', 'width':100})
    
#    sel_weekreport = select([WeekReport.c.title,System.c.abbr,
#                             WeekReport.c.description,],and_())
    weekreport_obj = WeekReport.filter(and_(WeekReport.c.begin_date>= first_day,
                                            WeekReport.c.end_date <= last_day,condition))
    data = []
    sys_data = {}
#    aaaa = 0
    for row in weekreport_obj:
        sel_planexe = select([System.c.abbr,Kfzx.c.name,Kfzx.c.id],
                          and_(PlanExecuteHis.c.weekreport == row.id,
#                               PlanExecuteHis.c.type == '1',
                               System.c.id == PlanExecuteHis.c.system,
                               condition_kfzx,
                               Kfzx.c.id == System.c.kfzx,
                               PlanExecute.c.id == PlanExecuteHis.c.old_id,
                               or_(and_(PlanExecute.c.sync_date != None,
                                        or_(PlanExecute.c.sync_date<first_day,
                                        PlanExecute.c.sync_date > last_day)),
                                    PlanExecute.c.sync_date == None,
                                    or_(PlanExecute.c.desc == None,PlanExecute.c.desc == '')))).group_by(PlanExecuteHis.c.system)
        sel_planexe_rs = sel_planexe.execute()
#        print '---------',sel_planexe
        sys_data.setdefault(row.id,{})
#        print '======',sel_planexe
        if row.description and row.description != '':
            flag = u'已填写'
        else:
            flag = u'未填写'

        record_counter = 0
#        aaaa = aaaa +1
        for row1 in sel_planexe_rs:
            system_abbr,kfzx_name,kfzx_id =  row1
#            print '----------',system_abbr,kfzx_name,kfzx_id
            b = sys_data[row.id].setdefault(kfzx_id,{})
#            b.setdefault('weekreport',row.title)
            aa = '<a href="/weekreport/view?id='+ str(row.id)+'">'+row.title+'</a>'

            b.setdefault('weekreport',aa)
            b.setdefault('weekreport_title',row.title)
            b.setdefault('kfzx_name',kfzx_name)
            b.setdefault('weekreport_desc',flag)
            b.setdefault('system_abbr',u'')
#            if ';'+system_abbr+';' in sys_data[row.id][kfzx_id]['system_abbr']:
#                pass
#            else:
#                sys_data[row.id][kfzx_id]['system_abbr'] = sys_data[row.id][kfzx_id]['system_abbr'] + system_abbr + u';'
            sys_data[row.id][kfzx_id]['system_abbr'] = sys_data[row.id][kfzx_id]['system_abbr'] + system_abbr + u' ; '
            b.setdefault('kfzx_num',0)
            sys_data[row.id][kfzx_id]['kfzx_num'] = sys_data[row.id][kfzx_id]['kfzx_num'] + 1
            record_counter = record_counter + 1
#            print 'ffffffffffff',sys_data
    
        if record_counter == 0:
            b = sys_data[row.id].setdefault(row.main_sys._kfzx_,{})
            aa = '<a href="/weekreport/view?id='+ str(row.id)+'">'+row.title+'</a>'
            
            b.setdefault('weekreport',aa)
            b.setdefault('weekreport_title',row.title)
            b.setdefault('kfzx_name',row.main_sys.kfzx.name)
            b.setdefault('weekreport_desc',flag)
            b.setdefault('system_abbr',u'')
            b.setdefault('kfzx_num',0)
#    print 'vvvvvvvvvv',aaaa    
    for x,y in sys_data.items():
        for z,q in y.items():
            data.append(q)
    data.sort(lambda p, q: cmp(p['kfzx_num'], q['kfzx_num']))
    data.reverse()
    def weekreport_title(value, obj):
#        print 'mmmmm',obj
        return obj['weekreport_title']
    pageno = int(request.values.get('page', 1)) - 1
    rows_per_page = int(request.values.get('rows', settings.get_var('PARA/ROWS_PER_PAGE', 10)))
#    print '+++',data
    #展示        
    view =  SimpleListView(header, data, id='req_count_table',#table_width=True,
        pagination=True, pageno=pageno,
        rows_per_page=rows_per_page)
    
    if 'download' in request.GET:        
        fields_convert_map = {'weekreport':weekreport_title}
        return view.download('weekreport_integrity_check_report.xls', fields_convert_map = fields_convert_map, action='download', timeout=0)
    elif 'data' in request.values:
        return json(view.json())
    else:
        result = view.run(head=True, body=True)
        result['table'] = view
        result.update({'query_form':query.form})
        return result
    
#管理类报表-本周计划执行情况明细查询
@expose('/charts/weekreport/current/week/desc/query')
def charts_weekreport_current_week_desc_query():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from query import create_weekreport_check_query
    from uliweb.core.html import Tag
    from decimal import Decimal
    from datetime import date
    
    WeekReport = get_model('weekreport')
    PlanExecute = get_model('planexecute')
    PlanExecuteHis = get_model('planexecutehis')
    System = get_model('system')
    Kfzx = get_model('kfzx')
    Plan = get_model('plan')
    Assignment = get_model('assignment')
    Tasks_Develop = get_model('tasks_develop')
    query = create_weekreport_check_query(url_for(charts_weekreport_integrity_check_report))
    c = query.run()

    condition = None
    condition_assignment = None
#    if (request.user.is_gm)and(request.user.department=='2'):
#        c['kfzx'] = request.user._kfzx_
    if c.get('title'):
        condition_assignment = (WeekReport.c.title.like('%'+c['title']+'%')) & condition_assignment
    if c.get('care_level'):
        condition = (WeekReport.c.care_level == int(c['care_level'])) & condition
    
    if c.get('kfzx'):
        condition = (Tasks_Develop.c.kfzx == int(c['kfzx'])) & condition
    
    if c.get('begin_date'):
        today = c['begin_date']
    else:
        today = date.today()
    
    if request.user.department=='2' and not request.user.is_gm:
        condition = (System.c.id.in_(request.user.users_systems.ids())) & condition
    
    first_day = today - datetime.timedelta(days=(today.weekday()+2)%7)
    last_day = first_day + datetime.timedelta(days=6)
#    print '44444444444',today,first_day,last_day
    #生成表头
    header = []
    header.append({'name':'Assignment_name','verbose_name':u'任务书名称', 'width':200})
    header.append({'name':'requirement_name','verbose_name':u'事项名称', 'width':200})
    header.append({'name':'system_abbr','verbose_name':u'系统简称', 'width':70})
    header.append({'name':'task_name','verbose_name':u'开发任务名称', 'width':70})
    header.append({'name':'kfzx','verbose_name':u'开发中心', 'width':300})
    header.append({'name':'plan','verbose_name':u'计划名称', 'width':100})
    header.append({'name':'desc','verbose_name':u'完成情况说明', 'width':200})
    
#    sel_weekreport = select([WeekReport.c.title,System.c.abbr,
#                             WeekReport.c.description,],and_())
    weekreport_obj = WeekReport.filter(and_(WeekReport.c.begin_date>= first_day,
                                            WeekReport.c.end_date <= last_day,condition_assignment))
    data = []
    
#    aaaa = 0
    for row in weekreport_obj:
        
        sel_planexe = select([Assignment.c.name,System.c.abbr,Tasks_Develop.c.name,
                              Kfzx.c.name,Plan.c.title,PlanExecute.c.desc],
                          and_(PlanExecuteHis.c.weekreport == row.id,
#                               PlanExecuteHis.c.type == '1',
                               System.c.id == PlanExecuteHis.c.system,
                               Kfzx.c.id == System.c.kfzx,
                               PlanExecute.c.id == PlanExecuteHis.c.old_id,
                               Plan.c.id == PlanExecuteHis.c.plan,
                               Tasks_Develop.c.id == PlanExecuteHis.c.task,
                               Assignment.c.id == Tasks_Develop.c.assignment,
                               condition,
                               )).group_by(Assignment.c.id,PlanExecuteHis.c.system)
        sel_planexe_rs = sel_planexe.execute()

        for row1 in sel_planexe_rs:
            Assignment_name,system_abbr,task_name,kfzx,plan,desc =  row1
            sys_data = {}
            sys_data['Assignment_name'] = Assignment_name
            sys_data['requirement_name'] = row.title
            sys_data['system_abbr'] = system_abbr
            sys_data['task_name'] = task_name
            sys_data['kfzx'] = kfzx
            sys_data['plan'] = plan
            sys_data['desc'] = desc
            data.append(sys_data)
            
#    print 'vvvvvvvvvv',aaaa    
#    for x,y in sys_data.items():
#        for z,q in y.items():
#            data.append(sys_data)
#    data.sort(lambda p, q: cmp(p['kfzx_num'], q['kfzx_num']))
#    data.reverse()
#    def weekreport_title(value, obj):
##        print 'mmmmm',obj
#        return obj['weekreport_title']
    pageno = int(request.values.get('page', 1)) - 1
    rows_per_page = int(request.values.get('rows', settings.get_var('PARA/ROWS_PER_PAGE', 10)))
#    print '+++',data
    #展示        
    view =  SimpleListView(header, data, id='req_count_table',#table_width=True,
        pagination=True, pageno=pageno,
        rows_per_page=rows_per_page)
    
    if 'download' in request.GET:        
#        fields_convert_map = {'weekreport':weekreport_title}
        return view.download('weekreport_current_week_desc_query.xls', action='download', timeout=0)
    elif 'data' in request.values:
        return json(view.json())
    else:
        result = view.run(head=True, body=True)
        result['table'] = view
        result.update({'query_form':query.form})
        return result


    
#自主研发优秀模块冠名活动-评选结果统计表
@expose('/charts/selfdev/modules/poll/counting')
def charts_selfdev_modules_poll_counting():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from uliweb.core.html import Tag
    from decimal import Decimal
    from datetime import date
    from uliweb import settings
    from uliweb.utils.common import get_var,get_choice

    Kfzx = get_model('kfzx')
    Selfdev_selection = get_model('selfdev_selection')
    Selfdevpoll_modules = get_model('selfdevpoll_modules')
    Selfdevpoll_user = get_model('selfdevpoll_user')

    condition = None
#    if (request.user.is_gm)and(request.user.department=='2'):
#        c['kfzx'] = request.user._kfzx_
    
    #生成表头
    header = []
    

    header.append({'name':'name','verbose_name':u'模块名称', 'width':120})
    header.append({'name':'module_kfzx','verbose_name':u'申报中心', 'width':80})
    header.append({'name':'award','verbose_name':u'奖项', 'width':80})
    header.append({'name':'grade','verbose_name':u'模块得分', 'width':80})
    header.append({'name':'poll_kfzx','verbose_name':u'投票部门', 'width':80})
    header.append({'name':'poll_num','verbose_name':u'得票数', 'width':80})
    
    sel = select([Selfdevpoll_modules.c.name,
                Selfdevpoll_modules.c.kfzx,
                Selfdev_selection.c.award,
                Selfdev_selection.c.kfzx,
                func.count(Selfdev_selection.c.id)],
                and_(Selfdevpoll_modules.c.id == Selfdev_selection.c.module,
                    Selfdevpoll_user.c.id == Selfdev_selection.c.user,
                    Selfdevpoll_user.c.enabled == True)
                ).group_by(Selfdev_selection.c.module,Selfdev_selection.c.award,Selfdev_selection.c.kfzx)
    
    sel_rs = sel.execute()
    
    data = []
    
    for row in sel_rs:
        name,module_kfzx,award,poll_kfzx,poll_num = row
        d = {}
        d['name'] = name
        d['module_kfzx'] = module_kfzx
        d['grade'] = poll_num
        d['poll_kfzx'] = poll_kfzx
        d['award'] = get_choice(settings.get_var('SELFDEVPOLL/awards'), award)
        
        d['poll_num'] = poll_num
        
        data.append(d)
    #取各中心人数平均数
    avg_kfzx = settings.get_var('SELFDEVPOLL/KFZX_RESOURCE_AVG')
    def grade(value,obj):
        if obj['module_kfzx'] == obj['poll_kfzx']:
#            print '--------',obj['poll_kfzx'],avg_kfzx
            resource_num=Kfzx.get(Kfzx.c.id == obj['poll_kfzx']).resource_num
            if resource_num and resource_num != 0:
                num = round((float(avg_kfzx)/resource_num)*value,2)
            else:
                num = value
            
        else:
            num = value
        return num
    def module_kfzx(value,obj):
        Kfzx = get_model('kfzx')
        return Kfzx.get(value).name
    def poll_kfzx(value,obj):
        Kfzx = get_model('kfzx')
        return Kfzx.get(value).name
    
    fields_convert_map = {'grade':grade,'module_kfzx':module_kfzx,'poll_kfzx':poll_kfzx}
    pageno = int(request.values.get('page', 1)) - 1
    rows_per_page = int(request.values.get('rows', settings.get_var('PARA/ROWS_PER_PAGE', 10)))
#    print '+++',data
    #展示        
    view =  SimpleListView(header, data, id='selfdev_modules_poll_counting',#table_width=True,
        pagination=True, pageno=pageno,fields_convert_map=fields_convert_map,
        rows_per_page=rows_per_page)
    
    if 'download' in request.GET:        
#        fields_convert_map = {'weekreport':weekreport_title}
        return view.download('selfdev_modules_poll_counting.xls', action='download', timeout=0)
    elif 'data' in request.values:
        return json(view.json())
    else:
        result = view.run(head=True, body=True)
        result['table'] = view
#        result.update({'query_form':query.form})
        return result

#自主研发优秀模块冠名活动-投票参与统计表
@expose('/charts/selfdev/kfzx/poll/counting')
def charts_selfdev_kfzx_poll_counting():
    from uliweb.orm import get_model,and_
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from uliweb.core.html import Tag
    from decimal import Decimal
    from datetime import date
    from uliweb import settings
    from uliweb.utils.common import get_var
    
    Kfzx = get_model('kfzx')
    Selfdev_selection = get_model('selfdev_selection')
    Selfdevpoll_modules = get_model('selfdevpoll_modules')
    Selfdevpoll_user = get_model('selfdevpoll_user')


    condition = None
#    if (request.user.is_gm)and(request.user.department=='2'):
#        c['kfzx'] = request.user._kfzx_
    
    #生成表头
    header = []
    

    header.append({'name':'kfzx','verbose_name':u'部门', 'width':120})
    header.append({'name':'kfzx_num','verbose_name':u'部门人数', 'width':80})
    header.append({'name':'poll_num','verbose_name':u'投票人数', 'width':80})
    header.append({'name':'poll_rate','verbose_name':u'投票比例', 'width':80})
    
    sel = select([Selfdev_selection.c.kfzx,
                func.count(func.distinct(Selfdev_selection.c.user))],
                and_(Selfdev_selection.c.user.in_(
                     select([Selfdevpoll_user.c.id],
                     Selfdevpoll_user.c.enabled == True)))
                ).group_by(Selfdev_selection.c.kfzx)
#    print '=====',sel
    sel_rs = sel.execute()
    
    data = []
    
    for row in sel_rs:
        poll_kfzx,poll_num = row
#        print 'poll_kfzx,poll_num',poll_kfzx,poll_num
#        a=Kfzx.get(Kfzx.c.id==poll_kfzx)
#        name =a.name
#        print '---',name
        d = {}
        d['kfzx'] = Kfzx.get(Kfzx.c.id==poll_kfzx).name
        d['kfzx_num'] = Kfzx.get(Kfzx.c.id==poll_kfzx).resource_num
        d['poll_num'] = poll_num
        if d['kfzx_num'] !=0 :
            d['poll_rate'] = round(float(poll_num)/d['kfzx_num'],4)
        else:
            d['poll_rate'] = 0
        
        data.append(d)
    
    def poll_rate(value,obj):
        return str(obj['poll_rate']*100)+'%'

        
    fields_convert_map = {'poll_rate':poll_rate}
    pageno = int(request.values.get('page', 1)) - 1
    rows_per_page = int(request.values.get('rows', settings.get_var('PARA/ROWS_PER_PAGE', 10)))
#    print '+++',data
    #展示        
    view =  SimpleListView(header, data, id='selfdev_modules_poll_counting',#table_width=True,
        pagination=True, pageno=pageno,fields_convert_map=fields_convert_map,
        rows_per_page=rows_per_page)
    
    if 'download' in request.GET: 
               
#        fields_convert_map = {'weekreport':weekreport_title}
        return view.download('charts_selfdev_kfzx_poll_counting.xls', action='download', timeout=0)
    elif 'data' in request.values:
        return json(view.json())
    else:
        result = view.run(head=True, body=True)
        result['table'] = view
#        result.update({'query_form':query.form})
        return result

#准开发需求变更查询
@expose('/charts/requirement/change/week/query')
def charts_requirement_change_week_query():
    from uliweb.orm import get_model,and_
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    from uliweb.core.html import Tag
    from decimal import Decimal
    from datetime import date
    from uliweb import settings
    from uliweb.utils.common import get_var
    from uliweb.utils.textconvert import text2html
    from uliweb.core.js import json_dumps
    import pickle
    from uliweb.utils.common import get_choice
    import time
    from query import create_requirement_change_week_query
    
    Audit = get_model('audit')
    Requirement = get_model('requirement')
    #获取一周起止日期
    
    query = create_requirement_change_week_query(url_for(charts_requirement_change_week_query))
    c = query.run()

    if c.get('begin_date'):
        begin_Date = c['begin_date']
    else:
        begin_Date = date.today()
    
#    #需要换为查询时间
#    begin_Date,end_Date = get_date()
    week_first_day = begin_Date - datetime.timedelta(days=(begin_Date.weekday()+2)%7)
    week_last_day = week_first_day + datetime.timedelta(days=6)
#    print '----------',week_first_day
#    print '+++++++++++',week_last_day
#    def strtodatetime(datestr,format):       
#        return datetime.date(*time.strptime(datestr, format)[:3])
#    
#    today= strtodatetime('2011-10-19',"%Y-%m-%d")
#    week_first_day = today - datetime.timedelta(days=(today.weekday()+2)%7)
#    week_last_day = week_first_day + datetime.timedelta(days=6)
    
    audit_obj = Audit.filter(and_(Audit.c.table_id == 2,Audit.c.modified_date >=week_first_day,Audit.c.modified_date <=week_last_day))
    
    header=[]
    header.append({'name':'modify_flag','verbose_name':u'变动类型', 'width':80})
    header.append({'name':'tx','verbose_name':u'条线', 'width':80})
    header.append({'name':'kfzx','verbose_name':u'牵头中心', 'width':80})
    header.append({'name':'req_mode','verbose_name':u'事项类型', 'width':100})
    header.append({'name':'req_title','verbose_name':u'事项名称', 'width':150})
    header.append({'name':'req_memo','verbose_name':u'备注', 'width':280})
    
    data=[]
    for row in audit_obj:
        data_tmp = {}
#        print 'llllllll',row.id,row.obj_id
        if row.changed_value:
            changed_value = pickle.loads(row.changed_value)
        else:
            changed_value ={}
#        print '+++++++++++',row.obj_id,row.modify_flag,changed_value.has_key('stage')
        req_obj = Requirement.get(row.obj_id)
#        print 'kkkkkkkk',req_obj
        if req_obj:
            if row.modify_flag == '1':
                data_tmp['modify_flag'] = u'新建'
                data_tmp['tx'] = req_obj.tx.name
                data_tmp['req_mode'] = get_choice(settings.get_var('PARA/REQ_MODE'), req_obj.mode)
                data_tmp['req_title'] = req_obj.req_id+'-'+req_obj.title
                data_tmp['req_memo'] = req_obj.memo
                data_tmp['kfzx'] = req_obj.main_sys.kfzx.name
                data.append(data_tmp)
            elif row.modify_flag == '2' and changed_value.has_key('stage'):
                data_tmp['modify_flag'] = get_choice(settings.get_var('PARA/REQ_STAGE').get(req_obj.mode, []), req_obj.stage)
                data_tmp['tx'] = req_obj.tx.name
                data_tmp['req_mode'] = get_choice(settings.get_var('PARA/REQ_MODE'), req_obj.mode)
                data_tmp['req_title'] = req_obj.req_id+'-'+req_obj.title
                data_tmp['req_memo'] = req_obj.memo
                data_tmp['kfzx'] = req_obj.main_sys.kfzx.name
                data.append(data_tmp)
#    print 'data',data        
    pageno = int(request.values.get('page', 1)) - 1
    rows_per_page = int(request.values.get('rows', settings.get_var('PARA/ROWS_PER_PAGE', 10)))
#    print '+++',data
    #展示        
    view =  SimpleListView(header, data, id='charts_requirement_change_week_query',#table_width=True,
        pagination=True, pageno=pageno,
        rows_per_page=rows_per_page)
    
    if 'download' in request.GET: 
               
#        fields_convert_map = {'weekreport':weekreport_title}
        return view.download('charts_requirement_change_week_query.xls', action='download', timeout=0)
    elif 'data' in request.values:
        return json(view.json())
    else:
        result = view.run(head=True, body=True)
        result['table'] = view
        result['begin_date'] = begin_Date
#        result['end_date'] = end_Date
        
        result.update({'query_form':query.form})
        return result
            
            
            